Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    1

    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,
    O.NAME, O.PARTNUM
    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

    where do I fit my left outer join clause?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a, b, c

    x, y, z

    PARTS and ORDERS (bad join condition, by the way)

    then a, b, c, d, e, f,

    which tables do you want to see in the answer?

    okay, i'll make up some too
    Code:
    select f.foo
         , b.bar
         , q.qux
      from footable as f
    left outer
      join bartable as b
        on f.id = b.fid
    left outer
      join quxtable as q
        on b.id = q.bid
    if you have more than 3 tables, just keep going, follow that pattern
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    same can be said for INNER joins

    it all depends on how the columns used in the joins are indexed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •