JOIN 4 TABLES with UNIQUE ID

  • vamshi.sql

    SSCommitted

    Points: 1717

    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 1 month, 2 weeks ago by  vamshi.sql.
  • frederico_fonseca

    SSChampion

    Points: 14297

    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: 1717

    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: 243910

    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 1 month, 2 weeks ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • vamshi.sql

    SSCommitted

    Points: 1717

    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

    Say Hey Kid

    Points: 666

    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: 1717

    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: 995462

    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]

    Result

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

    Result

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • frederico_fonseca

    SSChampion

    Points: 14297

    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)
  • vamshi.sql

    SSCommitted

    Points: 1717

    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 10 (of 10 total)

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