SQL Syntax/Logic? Question

johneliot · #1 (**permalink**) 11-15-05, 00:22

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

madafaka · #2 (**permalink**) 11-15-05, 04:41

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
     );

johneliot · #3 (**permalink**) 11-16-05, 19:50

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

madafaka · #4 (**permalink**) 11-17-05, 08:00

SELECT DISTINCT * FROM...

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

Internet Services

Web Development

Digital Marketing

Consumer Tech