Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    17

    Question Unanswered: Using Outer Join in Multiple Table Query

    What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:

    SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1]

    FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7]
    WHERE
    [table4].[field2]=[table1.field2] {this is an inner join}
    [table4].[field2]=[table2.field2] {this is an inner join}
    [table4].[field2]=[table3.field2] {this is an inner join}
    [table4].[field2]=[table5.field2] {this is an inner join}
    [table5].[field3]=[table6.field2] {this is an inner join}
    [table5].[field4]=[table7.field2] {this is needs to be a left join}

    As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    FROM	[Table4]
    	INNER JOIN [Table1] on [table4].[field2]=[table1.field2]
    	INNER JOIN [Table2] on [table4].[field2]=[table2.field2]
    	INNER JOIN [Table3] on [table4].[field2]=[table3.field2]
    	INNER JOIN [Table5] on [table4].[field2]=[table5.field2]
    	INNER JOIN [Table6] on [table5].[field3]=[table6.field2]
    	LEFT OUTER JOIN [Table7] on [table5].[field4]=[table7.field2]
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    You can also use parentheses to explicitly defione which table joined in which order. See BOL for details.

    e.g.,

    FROM ([Table4]
    INNER JOIN [Table1] on [table4].[field2]=[table1.field2]
    INNER JOIN [Table2] on [table4].[field2]=[table2.field2]
    INNER JOIN [Table3] on [table4].[field2]=[table3.field2]
    INNER JOIN [Table5] on [table4].[field2]=[table5.field2]
    INNER JOIN [Table6] on [table5].[field3]=[table6.field2])
    LEFT OUTER JOIN [Table7] on [table5].[field4]=[table7.field2]
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

Posting Permissions

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