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.
lapuccaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.*
 , t1.name name_1
 , t2.name name_2
  ... etc ...
from table0 t0
join table1 t1
  on t1.id = t0.id
join table1 t2
  on t2.id = t0.id
 ... etc ...

Open in new window

0
SharathData EngineerCommented:
>> 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.*,
       t1.name name_1,
       t2.name name_2
       ....etc...
  FROM table0 t0
  LEFT JOIN table1 t1 ON t0.id = t1.id
  LEFT JOIN table1 t2 ON t0.id = t2.id
  .... etc....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lapuccaAuthor Commented:
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?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
if there is no match, use indeed LEFT JOIN instead of simply JOIN.
0
SharathData EngineerCommented:
yes, you have to use LEFT JOIN. check my post.
0
lapuccaAuthor Commented:
thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.