JOIN 4 TABLES with UNIQUE ID

Question

Post reply

JOIN 4 TABLES with UNIQUE ID

vamshi.sql

SSCommitted

Points: 1770
More actions
December 1, 2019 at 6:29 pm
Go to Answer
#3703285
Hello All,
I have 4 tables with ID as common in all the tables. ID is the Unique key that connects all these 4 tables. When I try to select the data from all the tables using below query, I am getting id column for 4 times in the result. Could you please help me out to fix the issue.

Table 1 Contains 40 Columns with ID as unique key
Table 2 Contains 7 Columns with ID as unique key
Table 3 Contains 6 Columns with ID as unique key
Table 4 Contains 2 Columns with ID as unique key

I am using below query to get all the data from 4 tables.

SELECT * FROM table1 a
INNER JOIN table2 b ON a.id=b.id
INNER JOIN table3 c ON b.id=c.id
INNER JOIN table4 d ON c.id=d.id;
- This topic was modified 5 years ago by vamshi.sql.

frederico_fonseca

SSCoach

Points: 16164
More actions
December 7, 2019 at 6:25 pm
Answer
#3705685
you need to learn to explain your requirements better - just stating that for a small sample of data you need this output is not enough.
Even with the latest output you have although we can give you a solution that will solve that particular case what will happen when, for example, you have 2 ID's on same table with different values?
learn your data - do learn how to do SQL - there are plenty of online courses (free and paid) for you to take.
This particular example is really something that even a junior developer should already know - and if we give you a solution you still won't really understand the why it was done like it.
Below does give the desired output - except for column sec as we don't even know where that is from
```
select coalesce(a.id, b.id, c.id, d.id) as id
     , max(a.name) as Name
     , max(b.dept_id) as Dept_Id
     , max(c.mail) as Mail
     , max(d.contact) as Contact
from #Table1 a
full outer join #Table2 b
    on a.id = b.id
full outer join #Table3 c
    on b.id = c.id
full outer join #Table4 d
    on c.id = d.id
group by coalesce(a.id, b.id, c.id, d.id)
```

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16164 More actions · Answer 1

off course you are getting 4 columns with name of ID - you are doing a Select * so all columns will show.

if you only wish to have a single ID column you need to specify each column by name

SELECT a.id
     , a.xxxx - all other columns you require
     , b.yyyy -- all colummns you required (excluding ID)
     , c.wwww -- all colummns you required (excluding ID)
     , e.zzzz -- all colummns you required (excluding ID)
FROM table1 a
INNER JOIN table2 b
    ON a.id=b.id
INNER JOIN table3 c
    ON b.id=c.id
INNER JOIN table4 d ON
    c.id=d.id
;

vamshi.sql SSCommitted Points: 1770 More actions · Answer 2

Thank you. It's worked. I need to insert the output into separate table. Could you please share any idea on that please.

Thanks again for your input.

Phil Parkin SSC Guru Points: 246976 More actions · Answer 3

vamshi.sql wrote:

Thank you. It's worked. I need to insert the output into separate table. Could you please share any idea on that please.
Thanks again for your input.

Here's my idea: an INSERT statement. With >1700 points, you should know something about these.

INSERT separate_table (column1, column2, ...)

SELECT column1, column2, ...

FROM etc etc

This reply was modified 5 years ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vamshi.sql SSCommitted Points: 1770 More actions · Answer 4

Hi @frederico_fonseca,

If we use below statement, only identical values are displaying. I need identical values based on id and union all should display. Could you please help me on this. Thanks in advance.

INNER JOIN table2 b ON a.id=b.id
INNER JOIN table3 c ON b.id=c.id
INNER JOIN table4 d ON c.id=d.id

Example:

Table 1
+--------+---------+
| id     | name    |
+--------+---------+
|      1 |   Vamsi |
|      2 | Krishna |
|      3 |     Sai |
|      4 |   Mohan |
+--------+---------+

Table 2
+--------+---------+
| id     | dept_id |
+--------+---------+
|      1 |     101 |
|      5 |     102 |
|      6 |     103 |
|      7 |     102 |
+--------+---------+

Table 3
+--------+---------+
| id     | mail    |
+--------+---------+
|      1 |   v@com |
|      8 |   a@com |
|      9 |   s@com |
|     10 |   r@com |
+--------+---------+

Table 4
+--------+---------+
| id     | Contact |
+--------+---------+
|      1 |   78987 |
|     11 |   78968 |
|     12 |   56887 |
|     13 |   44567 |
+--------+---------+

I need to display the result like below

+--------+---------++--------+---------+---------+
| id     | name    | dept_id | mail    | Contact
+--------+---------+---------+---------+---------+
|     1  |   vamsi |     101 |   v@com | 78987
|     2  |  Krishna|         |         |
|     3  |  sai    |         |         |
|     4  |  Mohan  |         |         |
|     5  |         |     102 |         |
|     6  |         |     103 |         |
         SO ON......
+--------+---------++--------+---------+---------+

kherald69 SSC Eights! Points: 971 More actions · Answer 5

You just need to change the INNER JOINs to FULL JOINs and change the a.id field in the SELECT portion to COALESCE(a.id, b.id, c.id, d.id) and give it the alias of id. You have to use the COALESCE function because you don't have a single table that has all of the IDs.

SELECT COALESCE(a.id, b.id, c.id, d.id) id
     , a.name, b.dept_id, c.mail, d.contact
FROM #Table1 a
FULL JOIN #Table2 b ON a.id=b.id
FULL JOIN #Table3 c ON b.id=c.id
FULL JOIN #Table4 d ON c.id=d.id

vamshi.sql SSCommitted Points: 1770 More actions · Answer 6

Hi Kherald,

Thanks allot for your response. I have an issue with your sample query.

SELECT COALESCE(a.id, b.id, c.id, d.id) id

, a.name, b.dept_id, c.mail, d.contact

FROM #Table1 a

FULL JOIN #Table2 b ON a.id=b.id

FULL JOIN #Table3 c ON b.id=c.id

FULL JOIN #Table4 d ON c.id=d.id

Above sample works when we have same id on all the tables. But not worked if we have same id on Table1, Table2 and Table4 only. If we do so, Table4 value is displaying separately (10th row) as below. [It should include in 3rd row]

Result

Could you please share some inputs to change the query. It should display like below..

Result

Thanks in advance.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 7

vamshi.sql wrote:

Hi Kherald,
Thanks allot for your response. I have an issue with your sample query.
SELECT COALESCE(a.id, b.id, c.id, d.id) id
, a.name, b.dept_id, c.mail, d.contact
FROM #Table1 a
FULL JOIN #Table2 b ON a.id=b.id
FULL JOIN #Table3 c ON b.id=c.id
FULL JOIN #Table4 d ON c.id=d.id
Above sample works when we have same id on all the tables. But not worked if we have same id on Table1, Table2 and Table4 only. If we do so, Table4 value is displaying separately (10th row) as below. [It should include in 3rd row]
Could you please share some inputs to change the query. It should display like below..
Thanks in advance.

That extra data isn't in the data you posted. If you want better help on this problem, help us help you. Read'n'heed the article at the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vamshi.sql SSCommitted Points: 1770 More actions · Answer 8

Hi frederico_fonseca,

Thanks allot for your help. Actually, I'm new to development and I have multiple requirements with the same case\data. I can't post multiple requirements individually. So, included all of them in same post and sorry for that. Next time, I will try to explain the requirement better. Many thanks to all of you.