asked on

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

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.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lapucca

ASKER

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?

Guy Hengel [angelIII / a3]

if there is no match, use indeed LEFT JOIN instead of simply JOIN.

Sharath S

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

lapucca

ASKER

thank you.