Unanswered: LEFT Join with multiple tables in SQL query
I need an SQL query to retrive columns from 3 related tables a,b,c.
The relations between these tables are in 3 other tables x, y,z.
Out of these table c may or may not have records so we need to relate it with an outer join. c is related to b through table z.
How should I write this query that involves a number of tables and a join.
When 2 tables are involved, it is simple to write a query such as
SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE,
FROM PART P
LEFT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54
Now as I have more than 2 tables, how do I write my SQL query?
say I have
select a.field, b.field, c.field from a, b, c, d, e, f where
a.f1=d.f1 and d.f2 = b.f2 and and c.f1=f.f1 and f.f2=b.f1
But beware... when you start introducing OUTER joins, you can quickly cause an explosion of the number of rows that appear in the query! (No matter how many rows are in the final result.)
For example, let's say that tables A, B, and C each have 1,000 records each. The possibility exists that at some point during query-processing the engine is wrestling with 1,000 * 1,000 * 1,000 records. This can quickly exhaust the resources of even the most powerful SQL engine. Whether or not this happens depends upon your exact query, but "the possibility exists" and it needs to be avoided.