JOIN 4 TABLES with UNIQUE ID
-
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 4 years, 4 months ago by vamshi.sql.
-
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
; -
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.
- 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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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.idExample:
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......
+--------+---------++--------+---------+---------+ -
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 -
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.
- 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) -
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) -
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply