Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Question Unanswered: SQL Syntax/Logic? Question

    Hi,

    I have a working SQL Query that does a search for some records from a particular table for me, its below.

    SELECT *
    FROM S12006ReferencesTable
    WHERE [S12006ReferencesTable].Reference NOT IN
    (SELECT [S12005ReferencesTable].Reference
    FROM S12005ReferencesTable
    WHERE [S12005ReferencesTable].Reference=[S12006ReferencesTable].Reference);

    Now what I have been trying to do is get the query to search in 2 tables for the records this is what i have been trying but I cant get the syntax right or else my logic is completly wrong.

    SELECT *
    FROM S12006ReferencesTable
    WHERE [S12006ReferencesTable].Reference NOT IN

    (SELECT (([S12005ReferencesTable].Reference) OR ([S22005ReferencesTable].Reference))
    FROM S12005ReferencesTable OR S22005ReferencesTable

    WHERE (([S12005ReferencesTable].Reference) OR ([S22005ReferencesTable].Reference)) = [S12006ReferencesTable].Reference);

    Im new to SQL and dont really even have a complete understanding of how the original working SQL query is working. Im am using it in Access.

    Anyhow hope someone can sed some light on my problem or even just help me understand the working SQL statment better.

    Cheers,
    John Eliot

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I assume you want to compare records from both tables. For this purpose you can use UNION ALL:
    Code:
    SELECT *
    FROM S12006ReferencesTable
    WHERE [S12006ReferencesTable].Reference NOT IN
         (SELECT [S12005ReferencesTable].Reference
           FROM S12005ReferencesTable 
           WHERE [S12005ReferencesTable].Reference = [S12006ReferencesTable].Reference
           UNION ALL
           SELECT  [S22005ReferencesTable].Reference 
           FROM S22005ReferencesTable
           WHERE [S22005ReferencesTable].Reference = [S12006ReferencesTable].Reference
         );
    or you can use AND operator in WHERE clause
    Code:
    SELECT *
    FROM S12006ReferencesTable
    WHERE [S12006ReferencesTable].Reference NOT IN
         (SELECT [S12005ReferencesTable].Reference
           FROM S12005ReferencesTable 
           WHERE [S12005ReferencesTable].Reference = [S12006ReferencesTable].Reference
         )
    AND [S12006ReferencesTable].Reference NOT IN
           (SELECT  [S22005ReferencesTable].Reference 
           FROM S22005ReferencesTable
           WHERE [S22005ReferencesTable].Reference = [S12006ReferencesTable].Reference
         );
    Last edited by madafaka; 11-15-05 at 06:05.

  3. #3
    Join Date
    Nov 2005
    Posts
    5
    I couldnt get either of those suggestions to work I tried the Union one and it said that "this operation is not allowed in sub queries" and highlighted everything from the Second SELECT statment in your example down.

    I tried your second one and that didnt give an error but it also didnt return any results

    I got some ideas from them tho and this is what i now have and seems to be working?


    SELECT *
    FROM S12006ReferencesTable
    WHERE (((S12006ReferencesTable.Reference) Not In
    (SELECT [S12005ReferencesTable].Reference
    FROM S12005ReferencesTable
    WHERE
    [S12005ReferencesTable].Reference=[S12006ReferencesTable].Reference)))
    OR
    (((S12006ReferencesTable.Reference) Not In (SELECT [S22005ReferencesTable].Reference
    FROM S22005ReferencesTable
    WHERE
    [S22005ReferencesTable].Reference=[S12006ReferencesTable].Reference)));


    But this returns duplicates I have had a look at another thread that suggested using distinct as a way of only returning unique results, but Im not sure how to use it with my query. Can you say, SELECT * DISTNCT?

    Thanx for your help so far
    Kind Regards,
    John Eliot

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    SELECT DISTINCT * FROM...

    BTW what DB do you run query against? I'd like to solve the syntax error in first statement

Posting Permissions

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