Post reply

Nested LEFT JOIN with WHERE?

  • August 10, 2011 at 9:01 am

    #255491

    Using a LEFT JOIN on a simple query;

    SELECT tbl_Codes.option_Desc, tbl_Codes.option_ID, tbl_Codes_Select.row_ID, tbl_Codes_Select.User_ID FROM tbl_codes LEFT JOIN tbl_Codes_Select ON tbl_Codes.option_ID=tbl_codes_select.option_ID ORDER BY tbl_codes.option_ID

    This is the resulting table;

    Is it possible to nest a WHERE on the User_ID? So if I use User_ID = 1 teh resulting set would look like this;

  • August 10, 2011 at 9:20 am

    #1366653

    LEFT JOIN ... ON tbl_Codes.option_ID=tbl_codes_select.option_ID AND tbl_Codes_Select.User_ID = 1

    If you put that in the where clause you'll then have a hidden inner join ( 1 != null )

  • August 10, 2011 at 9:46 am

    #1366680

    Error: -2147217900 Join expression not supported.

    SELECT tbl_Codes.option_Desc, tbl_Codes.option_ID, tbl_Codes_Select.row_ID, tbl_Codes_Select.User_ID FROM tbl_codes LEFT JOIN tbl_Codes_Select ON tbl_Codes.option_ID=tbl_codes_select.option_ID AND tbl_Codes_Select.User_ID = 1

  • August 10, 2011 at 9:52 am

    #1366690

    What application do you use to run this? Sql version (@@version)?

  • August 10, 2011 at 9:58 am

    #1366696

    Put the filter into the JOIN otherwise you will turn it into an INNER JOIN as Ninja shows.

    SELECT

    c.option_Desc,

    c.option_ID,

    s.row_ID,

    s.User_ID

    FROM tbl_codes c

    LEFT JOIN tbl_Codes_Select s

    ON c.option_ID = s.option_ID

    AND s.User_ID = 1

    ORDER BY c.option_ID

    Learn to use table aliases, they make code much more readable 🙂

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply