Fastest way to query all data from 3 tables
Let's say I have 3 tables:
Table 1
|Id|Date |Data1|
|--|---- |-----|
|1 |24/05|Some1|
Table 2
|Id|Date |Data2|
|--|---- |-----|
|1 |24/05|Some2|
|1 |12/06|Some2|
Table 3
|Id|Date |Data3|
|--|---- |-----|
|1 |24/05|Some3|
|1 |14/06|Some3|
How do I get the results like this:
|Id|Date |Data1|Data2|Data3|
|--|-----|-----|-----|-----|
|1 |24/05|Some1|Some2|Some3|
|1 |12/06|NULL |Some2|NULL |
|1 |14/06|NULL |NULL |Some3|
Using MS SQL.
What I came up with is this
SELECT Id, Date, Data1, Data2, Data3 FROM Table1
LEFT JOIN Table2 ON Table1.Id = Table2.Id AND Table1.Date = Table2.Date
LEFT JOIN Table3 ON Table1.Id = Table3.Id AND Table1.Date = Table3.Date
UNION
SELECT Id, Date, Data1, Data2, Data3 FROM Table2
LEFT JOIN Table1 ON Table2.Id = Table1.Id AND Table2.Date = Table1.Date
LEFT JOIN Table3 ON Table2.Id = Table3.Id AND Table2.Date = Table3.Date
UNION
SELECT Id, Date, Data1, Data2, Data3 FROM Table3
LEFT JOIN Table1 ON Table3.Id = Table1.Id AND Table3.Date = Table3.Date
LEFT JOIN Table2 ON Table3.Id = Table2.Id AND Table3.Date = Table2.Date
Is there a better way to query data?
+3
source to share
3 answers
select
id = coalesce(t1.id,t2.id,t3.id)
, [date] = coalesce(t1.[date],t2.[date],t3.[date])
, t1.Data1
, t2.Data2
, t3.Data3
from Table1 t1
full join Table2 t2
on t1.id = t2.id
and t1.[date]=t2.[date]
full join Table3 t3
on (t1.id = t3.id and t1.[date]=t3.[date])
or (t2.id = t3.id and t2.[date]=t3.[date])
Demo version of rexter: http://rextester.com/ATCJ12705
returns:
+----+-------+-------+-------+-------+
| id | date | Data1 | Data2 | Data3 |
+----+-------+-------+-------+-------+
| 1 | 24/05 | Some1 | Some2 | Some3 |
| 1 | 12/06 | NULL | Some2 | NULL |
| 1 | 14/06 | NULL | NULL | Some3 |
+----+-------+-------+-------+-------+
0
source to share
A quick way is to sort by all dates and IDs in the temporary table and then put the data against it.
Select T.id,T.date, T1.Data1,T2.Data2,T3.Data3
FROM
(
select id,date from Table1
UNION
select id,date from Table2
UNION
select id,date from Table3
) T
LEFT JOIN Table1 T1 on T.id=T1.id and T.date=T1.date
LEFT JOIN Table2 T2 on T.id=T2.id and T.date=T2.date
LEFT JOIN Table3 T3 on T.id=T3.id and T.date=T3.date
ORDER BY T.id,T.date
Number of JOINs = number of unique tables
0
source to share
Assuming each table only has one row for each id and date, entering null values ββin the UNION and selecting the MAX value can achieve the same:
create table #t1 (id int, [date] nvarchar(5), data1 nvarchar(10))
create table #t2 (id int, [date] nvarchar(5), data2 nvarchar(10))
create table #t3 (id int, [date] nvarchar(5), data3 nvarchar(10))
insert into #t1 values (1, '24/05', 'Some1')
insert into #t2 values (1, '24/05', 'Some2'), (1, '12/06', 'Some2')
insert into #t3 values (1, '24/05', 'Some3'), (1, '14/06', 'Some3')
select a.id, a.[date], max(a.data1) as [Data1], max(a.data2) as [Data2], max(a.data3) as [Data3]
from
(
select id, [date], [data1] as [Data1], Null as [Data2], null as [Data3] from #t1
union
select id, [date], null as [Data1], [data2] as [Data2], null as [Data3] from #t2
union
select id, [date], null as [Data1], Null as [Data2], [Data3] as [Data3] from #t3
) a
group by a.id, a.[date]
0
source to share