Joining 3 tables based on common columns

Question

I have four tables - tblBase, tblLookup, tblData and tblData2

tblBase

+---------+----------+-----------+------------+
| Base_ID | Base_Num | Base_Type | Base_Date  |
+---------+----------+-----------+------------+
|       1 |     1234 | ABC       | 01/05/2016 |
|       2 |     3456 | DEF       | 02/05/2016 |
|       3 |     7890 | GHI       | 03/05/2016 |
+---------+----------+-----------+------------+

tblLookup

+-----------+-------------+
| Lookup_ID | Lookup_Name |
+-----------+-------------+
|         1 | Apple       |
|         2 | Orange      |
|         3 | Banana      |
+-----------+-------------+

tblData

+-----------+----------+------------+
| Data_Name | Data_Num | Data_Date  |
+-----------+----------+------------+
| Apple     |     1234 | 02/05/2016 |
| Orange    |     3456 | 03/05/2016 |
| Guava     |     5937 | 04/05/2016 |
+-----------+----------+------------+

tblData2

+------------+-----------+------------+
| Data2_Name | Data2_Num | Data2_Date |
+------------+-----------+------------+
| Grapes     |      3953 | 02/05/2016 |
| Orange     |      3456 | 03/05/2016 |
| Banana     |      7890 | 04/05/2016 |
| Banana     |      1473 | 07/05/2016 |
+------------+-----------+------------+

I am trying to get the Data_Date from tblData or tblData2 (where ever the data exists) join with tblBase where Base_Num matches . As the common columns exists in tblLookup, I need to join all the four tables.

For example, Base_ID = 3, Base_Num = 7890, should pick up Data_Date from tblData2, as both Base_ID (Banana) and Base_Num (7890) matches.

I tried doing INNER JOIN however it did not give the desired result.

I'm looking for a resulting table like this:

+---------+----------+-----------+------------+-------------------+
| Base_ID | Base_Num | Base_Type | Base_Date  | Desired_Data_Date |
+---------+----------+-----------+------------+-------------------+
|       1 |     1234 | ABC       | 01/05/2016 | 02/05/2016        |
|       2 |     3456 | DEF       | 02/05/2016 | 03/05/2016        |
|       3 |     7890 | GHI       | 03/05/2016 | 04/05/2016        |
+---------+----------+-----------+------------+-------------------+

Would certainly help get your answer sooner if you edit your post to include the SQL you've tried already that's not producing the desired result — dinotom, May 02 '16 at 09:26
Dinotom, as INNER JOIN didn't give me the result, I tried JOIN. But I am able to join only one Data Table, not the both Data Tables. Here's my code: `SELECT A.*, C.Data_Date FROM tblBase A JOIN tblLookup B on A.Base_ID = B.Lookup_ID JOIN tblData C on B.Lookup_Name = C.Data_Name AND C.Data_Num = A.Base_Num` — Shanka, May 02 '16 at 09:33
Base_ID = 2 Base_num = 3456 (orange) result could come from tbldata or tbldata2 - how should this (and other duplicates) be handled? — P.Salmon, May 02 '16 at 09:34
Hi P.Salmon, only one should be considered, any one from tblData or tblData2. — Shanka, May 02 '16 at 09:38

score 1 · Accepted Answer · answered May 02 '16 at 09:39

You may try Left Join

SELECT B.Base_ID, B.Base_Num , B.Base_Type, B.Base_Date,
D1.Data_Name AS Data1, D1.Data_Date AS DESIRED_DATE1  
D2.Data2_Name AS Data2, D2.Data2_Date  AS DESIRED_ 

FROM tblBase B

JOIN tblLookup L ON L.Lookup_ID=B.Base_ID
LEFT JOIN tblData D1 ON D1.Data_Num = B.Base_Num 
LEFT JOIN tblData2 D2 ON D2.Data2_Num = B.Base_Num 
WHERE <Condition>

score 0 · Answer 2 · answered May 02 '16 at 10:23

declare @tblbase table (Base_ID int, Base_Num int, Base_Type varchar(3), Base_Date  varchar(10))
Insert into @tblbase
values 
(       1 ,     1234 , 'ABC', '01/05/2016'),
(       2  ,    3456 , 'DEF', '02/05/2016'),
(       3  ,    7890 , 'GHI', '03/05/2016')

declare @tblLookup table (Lookup_ID int, Lookup_Name varchar(10))
insert into @tblLookup      
values
(         1 , 'Apple' ),
(         2 , 'Orange'),
(         3 , 'Banana') 

declare @tbldata table (Data_Name varchar(10), Data_Num int, Data_Date varchar(10))
Insert into @tbldata
values
( 'Apple'  ,     1234 , '02/05/2016'),
( 'Orange' ,     3456 , '03/05/2016'),
( 'Guava'  ,     5937 , '04/05/2016')

declare @tbldata2 table (Data_Name varchar(10), Data_Num int, Data_Date varchar(10))
Insert into @tbldata2
values
( 'Grapes',           3953 , '02/05/2016'),
( 'Orange' ,          3456 , '03/05/2016'),
( 'Banana'  ,         7890 , '04/05/2016'),
( 'Banana'   ,        1473 , '07/05/2016')

/*
Expected result
+---------+----------+-----------+------------+-------------------+
| Base_ID | Base_Num | Base_Type | Base_Date  | Desired_Data_Date |
+---------+----------+-----------+------------+-------------------+
|       1 |     1234 | ABC       | 01/05/2016 | 02/05/2016        |
|       2 |     3456 | DEF       | 02/05/2016 | 03/05/2016        |
|       3 |     7890 | GHI       | 03/05/2016 | 04/05/2016        |
+---------+----------+-----------+------------+-------------------+
*/
select bt.*,u.data_date as Desired_data_date
from    @tblbase bt
join    @tblLookup  lu on lu.lookup_id = bt.base_id
join
(select t1.*    from @tbldata t1
union
select  t2.*    from @tbldata2 t2
)  u
on      u.data_name = lu.Lookup_Name
where   u.Data_Num = bt.Base_Num
order   by bt.Base_Date

Joining 3 tables based on common columns

2 Answers2