Joining multiple tables

[cocopud]

I need to write a query that will query data from 18 tables. Any suggestions on the best way to do this? Should I just list all the columns that I need and then proceed to join all the tables together.

I tried to start out with 2 tables and kept adding on so that I can check the results, but once I get half way to two thirds done, I get alot of null data, so I'm not sure if my query is correct, or it could be that there isn't any data that matches the joins once I start to join alot of tables.

Any suggestions would be appreciated. Also, would joins be the only way to do this and also, any suggestions on good books that would be helpful with writing complex SELECT/JOINS statements.

 

[foxdev]

Are there any tables that are largish (say, more than 300K rows)?

You may need to specify OUTER JOINs if the joined column is nullable, but that depends on whether you want them included or not.

In and of itself, 18 isn't tragic...but if several of them have many rows, performance can be poor. In some cases, it may help to pre-join some of the tables as @table/#tables.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[ESquared]

select all columns
add marker columns between each table
add one table at a time to the join

SELECT
   '- A --------------',
   A.*,
   '- B --------------',
   B.*
FROM
   A
   INNER JOIN B ON A.Q = B.Q

Hm. That looks okay. Next.

SELECT
   '- A --------------',
   A.*,
   '- B --------------',
   B.*,
   '- C --------------',
   C.*
FROM
   A
   INNER JOIN B ON A.Q = B.Q
   INNER JOIN C ON B.X = C.X

Oops. Something happened. Must be this join that broke it all.