How to easily create a select statement inner join 20 tables/

Posted on 2008-11-12
Last Modified: 2012-05-05
Hi, Is there a easy way to create a select statement that involves about 20 tables in a database?  In table0 I want to select everything.  Using the Id column in table0 I want to join that with rest of the table1-table20 which all have a id column as well.  I want all columns of table0 even if there is no match in any of the other table.  From Table1 - table20, I only want the column Name if the Id column is a match for the table 0.  Can someone show how this select query would look like?  Also, there is a lot of columns in table0, is there any way to specify to return all columns form table0 during a join?  I used to use Query AnalyZer but it's no longer avaialble in 2005.  Very annoying.
Question by:lapucca
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    yes, it would be like below.

    > I used to use Query AnalyZer but it's no longer avaialble in 2005.  
    in sql 2005 management studio, you have the query window.
    otherwise, you have the command line tool sqlcmd.

    alternatively, there are some nice (and free) tools out there.

    select t0.*
     , name_1
     , name_2
      ... etc ...
    from table0 t0
    join table1 t1
      on =
    join table1 t2
      on =
     ... etc ...

    Open in new window

    LVL 40

    Accepted Solution

    >> I only want the column Name if the Id column is a match for the table 0.
    if it matches it will display the column name otherwise NULL will be displayed. Is that what you want?

    You need to use left join.

    SELECT t0.*,
      FROM table0 t0
      LEFT JOIN table1 t1 ON =
      LEFT JOIN table1 t2 ON =
      .... etc....

    Author Comment

    So, just using join as inner join for all tables?  Would this return all fields in table0 (and other matching id tables' name field) when there is no match of id in table3 or table8?  I thougth inner join only return that row if there is a match on both tables or all tables join.  Is this not the case?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    if there is no match, use indeed LEFT JOIN instead of simply JOIN.
    LVL 40

    Expert Comment

    yes, you have to use LEFT JOIN. check my post.

    Author Closing Comment

    thank you.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now