Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: operator precedence challenge

    I'm having problems with the query below. It works fine until the "NOT IN" part and I'm not sure why. Basically, I'm getting records where their educ_audio field is set to "no". I've messed with the parens to try to force SQL to process the NOT IN part before the other clauses without avail. Can someone shed some light?

    TIA

    SELECT distinct contacts.fname, contacts.lname, contacts.company, contacts.contact_id, contacts.business_phone, contacts.emailAddress, contacts.dateLastContact FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON journal.product_code = products.product_code WHERE ( journal.product_code IN ('ABLE') ) OR (( journal.product_code IN ('JOBS') ) AND ( journal.product_status IN ('12','14','15') )) OR (( products.prod_design IN ('audio') ) AND ( products.library_code IN ('hrss') )) AND (journal.journal_id NOT IN (SELECT journal.journal_id FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON products.product_code = journal.product_code where ( contacts.educ_audio IN ('no') ) )) ORDER BY contacts.lname asc, contacts.fname

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you can get the results you want by enclosing all your OR clauses in one set of parenthesis. If I understand correctly, your records must satisfy at least one of the OR clauses, and the AND clause:

    SELECT distinct
    contacts.fname,
    contacts.lname,
    contacts.company,
    contacts.contact_id,
    contacts.business_phone,
    contacts.emailAddress,
    contacts.dateLastContact
    FROM journal
    INNER JOIN contacts ON journal.contact_id = contacts.contact_id
    INNER JOIN products ON journal.product_code = products.product_code
    WHERE ((journal.product_code IN ('ABLE'))
    OR ((journal.product_code IN ('JOBS')) AND (journal.product_status IN ('12','14','15')))
    OR ((products.prod_design IN ('audio')) AND (products.library_code IN ('hrss'))))
    AND (journal.journal_id NOT IN
    (SELECT journal.journal_id
    FROM journal
    INNER JOIN contacts ON journal.contact_id = contacts.contact_id
    INNER JOIN products ON products.product_code = journal.product_code
    WHERE (contacts.educ_audio IN ('no'))))
    ORDER BY contacts.lname asc,
    contacts.fname


    But you can clean this up a lot more
    First, IN ('ABLE') is equivalent to ='ABLE', so don't muddy the waters with more parentheses than you need.
    Second, many of your Parenthesis pairs are superfluous, in that they enclose only one clause. Eliminate the clutter.

    SELECT distinct
    contacts.fname,
    contacts.lname,
    contacts.company,
    contacts.contact_id,
    contacts.business_phone,
    contacts.emailAddress,
    contacts.dateLastContact
    FROM journal
    INNER JOIN contacts ON journal.contact_id = contacts.contact_id
    INNER JOIN products ON journal.product_code = products.product_code
    WHERE (journal.product_code = 'ABLE'
    OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
    OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
    AND journal.journal_id NOT IN
    (SELECT journal.journal_id
    FROM journal
    INNER JOIN contacts ON journal.contact_id = contacts.contact_id
    INNER JOIN products ON products.product_code = journal.product_code
    WHERE contacts.educ_audio = 'no')
    ORDER BY contacts.lname asc,
    contacts.fname

    --Lastly, consider converting you NOT IN clause to a LEFT OUTER JOIN subquery:

    SELECT distinct
    contacts.fname,
    contacts.lname,
    contacts.company,
    contacts.contact_id,
    contacts.business_phone,
    contacts.emailAddress,
    contacts.dateLastContact
    FROM journal
    INNER JOIN contacts ON journal.contact_id = contacts.contact_id
    INNER JOIN products ON journal.product_code = products.product_code
    LEFT OUTER JOIN
    (SELECT journal.journal_id
    FROM journal
    INNER JOIN contacts ON journal.contact_id = contacts.contact_id
    INNER JOIN products ON products.product_code = journal.product_code
    WHERE contacts.educ_audio = 'no') ExcludeRecords
    on journal.journal_id = ExludeRecords.journal_id
    WHERE (journal.product_code = 'ABLE'
    OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
    OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
    AND ExcludeRecords.journal_id is null
    ORDER BY contacts.lname asc,
    contacts.fname

    If you take the time to develop a coding style that includes neat and consistent indenting and formatting, you will be rewarded with much clearer and more bug-free code.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    Thanks for your help.

    Reason the SQL looks like it does (not indented, etc) is because that was a paste from my application. This is for an ad-hoc query tool I'm building.

    As to your suggestions, they all seem to work except the last one.
    Error: The column prefix 'ExludeRecords' does not match with a table name or alias name used in the query.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just a typo.

    on journal.journal_id = ExludeRecords.journal_id

    ...should have been:

    on journal.journal_id = ExcludeRecords.journal_id
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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