Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: convert old style join to ANSI?

    Hi,
    how can i write the following query in ANSI join syntax?

    SELECT cg.company_group_id
    FROM appuser.users_list ul,
    company_group cg
    WHERE ( ul.company_group_id IS NULL
    OR ul.company_group_id = cg.company_group_id)
    AND ul.user_id = 'hank';


    I thought of this:

    SELECT cg.company_group_id
    FROM appuser.users_list ul
    JOIN company_group cg
    ON ul.company_group_id = cg.company_group_id
    WHERE ul.company_group_id IS NULL
    AND ul.user_id = 'hank'

    but it returns nothing!

    and this:

    SELECT cg.company_group_id
    FROM appuser.users_list UL
    LEFT JOIN company_group CG
    ON ul.company_group_id = cg.company_group_id
    WHERE ul.company_group_id IS NULL
    AND ul.user_id = 'hank';

    returns everything!

    please help!
    our coding standard is ALL ANSI, all the time.

    thanks
    hank

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    First of all, I think your original statement is ANSI-compliant. However, it doesn't make a lot of sense to me anyway. What is the purpose of "ul.company_group_id IS NULL"? Can you explain in plain English what you are trying to achieve?

Posting Permissions

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