Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Pennsylvania, USA
    Posts
    2

    Unanswered: Simple Question: Change join statement to be SQL standard

    I have an join statement that works fine:
    SELECT one.Name
    FROM one, two
    WHERE one.ID *= two.one_id
    AND one.type = 'Foo'

    It returns like 100+ rows.

    I wanted to learn how to use the SQL-92 standard of doing joins, so I re-wrote my statement as:
    SELECT one.Name
    FROM one LEFT OUTER JOIN two ON one.id = two.one_id
    WHERE one.type = 'Foo'


    It looks like it matches the examples I've found in the docs, yet the second statement only returns 4 rows. It should return a lot more.

    What's wrong?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your code selects only Foo records from table one, and any matching records from table two.
    SELECT one.Name
    FROM one
    LEFT OUTER JOIN two ON one.id = two.one_id
    WHERE one.type = 'Foo'


    This selects all records from table one, and any matching records from table two for type-foo records.
    --Matching table two records for non-foo table one records are not returned.
    SELECT one.Name
    FROM one
    LEFT OUTER JOIN two ON one.id = two.one_id and one.type = 'Foo'

    In short, your code limits the number of table one records returned, while the second example only limits the number of records matched against table two.

    blindman

  3. #3
    Join Date
    Sep 2003
    Location
    Pennsylvania, USA
    Posts
    2
    Originally posted by blindman
    Your code selects only Foo records from table one, and any matching records from table two.
    SELECT one.Name
    FROM one
    LEFT OUTER JOIN two ON one.id = two.one_id
    WHERE one.type = 'Foo'


    This selects all records from table one, and any matching records from table two for type-foo records.
    --Matching table two records for non-foo table one records are not returned.
    SELECT one.Name
    FROM one
    LEFT OUTER JOIN two ON one.id = two.one_id and one.type = 'Foo'

    In short, your code limits the number of table one records returned, while the second example only limits the number of records matched against table two.

    blindman
    Ahh I understand. The WHERE clause was limiting the already joined data.

    Thanks

Posting Permissions

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