Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    Hamburg, Germany
    Posts
    14

    Red face Unanswered: Problem combining left join with where

    Hi all,

    Whenever I create a LEFT JOIN query and combine it with a WHERE clause the LEFT JOIN behaves like an INNER JOIN and I really can't figure out why!

    One query would be:

    SELECT
    IM_INCIDENTSM1.INCIDENT_ID,
    IM_SCRELATIONM1.DEPEND

    FROM
    IM_INCIDENTSM1 LEFT JOIN IM_SCRELATIONM1 ON (IM_INCIDENTSM1.INCIDENT_ID = IM_SCRELATIONM1.SOURCE)

    WHERE
    OPEN_TIME between #6/1/2003# And #6/30/2003# AND(IM_SCRELATIONM1.SOURCE_FILENAME = 'incidents');

    Please help me!!!

    Thanks a lot in advance,
    Ulf Moehring

  2. #2
    Join Date
    Sep 2002
    Location
    Finland
    Posts
    34
    I assume that reason is IM_SCRELATIONM1.SOURCE_FILENAME = 'incidents'

    Query result is rows that match join and Source_filename is 'incidents'.
    Change where clause:
    (IM_SCRELATIONM1.SOURCE_FILENAME = 'incidents' or IM_SCRELATIONM1.SOURCE_FILENAME Is Null)

    This will increase your result those recordt that have no match on join.
    However you don't see those record that have match on join but not match 'incidents'.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your where clause includes a reference to your secondary table:
    IM_SCRELATIONM1.SOURCE_FILENAME = 'incidents'
    Well, in a left join where the is no matching record in IM_SCRELATIONM1 then SOURCE_FILENAME is null and hence those rows are excluded from the result set.

    You have a couple of options. In standard SQL you can include the criteria as part of your join:

    ...ON (IM_INCIDENTSM1.INCIDENT_ID = IM_SCRELATIONM1.SOURCE)
    AND (IM_SCRELATIONM1.SOURCE_FILENAME = 'incidents')

    I think you can do this in Access as well.

    The second option is to select all the records from IM_SCRELATIONM1 where SOURCE_FILENAME = 'incidents' as a subquery and then left join on the results:

    SELECT
    IM_INCIDENTSM1.INCIDENT_ID,
    INCIDENTS.DEPEND

    FROM
    IM_INCIDENTSM1
    LEFT JOIN (Select DEPEND from IM_SCRELATIONM1 where SOURCE_FILENAME = 'incidents') as INCIDENTS ON (IM_INCIDENTSM1.INCIDENT_ID = INCIDENTS.SOURCE)

    WHERE
    OPEN_TIME between #6/1/2003# And #6/30/2003#

    By the way,
    mns' solution is simpler as long as there aren't any rows in IM_SCRELATIONM1 where SOURCE_FILENAME actually is null, in which case these would show up in your result set. The above two solutions should exclude them.

    blindman
    Last edited by blindman; 07-09-03 at 17:44.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...come to think of it, a variation on mns' solutions might cover null values as well:
    (IM_SCRELATIONM1.SOURCE_FILENAME = 'incidents' or IM_SCRELATIONM1.SOURCE Is Null)

    ...checking for a null value in the keyfield IM_SCRELATIONM1.SOURCE instead. These couldn't ever be null and still link to the primary table.

    blindman

Posting Permissions

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