Selecting only values from one table that do NOT exist in another?

Selecting only values from one table that do NOT exist in another?

Which means, I have two tables, one of the tables already contains part of the 2nd tables data, and I now want a sql-query (for access) that will return the rows that do NOT exist in the first table (from the 2nd table).

Is that easy? It sounds easy, but I just can't figure it out

Something like


//NoXcuz

This is what it would look like if I understood it correctly, however, it returns nothing.

Probably because I have no null's in my table..
I need the rows that do not exist in the first table, but these rows do not contain nulls or any other character to check by.

SELECT
EXCEL_TEMP_PUR.*
FROM
EXCEL_118
LEFT JOIN
EXCEL_TEMP_PUR
ON
((EXCEL_118.PROJID = EXCEL_TEMP_PUR.PROJID) AND (EXCEL_118.WORK_NUMBER = EXCEL_TEMP_PUR.WORK_NUMBER)
AND (EXCEL_118.LAJI = EXCEL_TEMP_PUR.LAJI) AND (EXCEL_118.ORDERID = EXCEL_TEMP_PUR.ORDERID)
AND (EXCEL_118.ORDER_ROWNUM = EXCEL_TEMP_PUR.ORDER_ROWNUM) AND (EXCEL_118.ITEMID = EXCEL_TEMP_PUR.ITEMID)
AND (EXCEL_118.ITEMNAME = EXCEL_TEMP_PUR.ITEMNAME) AND (EXCEL_118.COMPANYNAME = EXCEL_TEMP_PUR.COMPANYNAME)
AND (EXCEL_118.SIDOTUT = EXCEL_TEMP_PUR.SIDOTUT))
WHERE
EXCEL_118.PROJID IS NULL

Sorry, I mixed things up a bit...
It should be something like this instead (swap table1 and table2 in the FROM part)


Also, the NULLs doesn't exist in your tables, but in your result set when you do a left join. This left join means that 'give me everything from the left table joined with the right table regardless if there's a match or not'. And when you add the IS NULL part, you filter it out so that you see only those entries in the left table that doesn't have a corresponding entry in the right table.

And you shouldn't use all those fields to join the tables, but stick to the primary key that determines the uniqueness of the row (probably PROJID in your case).

//NoXcuz

Good grief, it was that simple then?

As in, it works, once I switched the tables as you told me to, and now it returns the rows needed...

Thank you NoXcuz
(tack, du är från sverige såg jag just )

Hey again, hope you are ready to help again, this time it's a bit trickier..

The same query does the job, however it returns 93 rows too much, which I can't explain in any way...

So, here we go:

That is the query I am using right now, EXCEL_118 table contains the matched values that I want to exclude in this query, exclude from EXCEL_TEMP_PUR table.

EXCEL_118 contains 5919 rows while
EXCEL_TEMP_PUR has 61937 rows
which gives a total of 56018 unmatched rows (rows that were not included in EXCEL_118 table).

Thus, the query above, _should_ return 56018 rows, however it does not, it returns too many?!, 56111 rows.
That is 93 rows too many.

I have tried doing the query without checking for null's in some of those fields, for example only checking PROJID field for null's, it doesn't matter, the result will still be 56111 rows.


Further information about EXCEL_TEMP_PUR table (the source table for all the data important to this query).
It contains null values in WORK_NUMBER fields, 15711 rows
and null values in LAJI field, 205 rows
and null values in ORDERID field, 42 rows.

Can any of those nulls in the source table affect the query, or where might the problem be?

Hena

I think the query does it's job, so the problems must be somewhere in your data. You have something in your tables that you're not expecting. Also, I'm not fully aware of your table layout, which adds to the 'trickieness'...
Simply put: You need to find out which those 93 rows are, so you can determine what to do with them or how to change the query.
Try something like this and see if you get any results:


Also, what happens if you remove the fields that can be NULL from the join part, and only use PROJID to join them. Still the same amount of rows in the result set?
Or if you use a single field to join them by, run the query once for each field and change that field in the query to see if the queries return the same amount of rows.

//NoXcuz

So true, the query works..
The underlying data was the problem, in fact, there is a group by problem that I have to solve now instead...
The group by in this query only uses projid to order_rownum to group, while the one that made the TEMP_PUR table used 3 more fields (because sql (or access) requiers them to be in).

I'll have to solve this in someway...

You wouldn't happen to know how to easily avoid the problem I have in this query do you?



I want to NOT group with ITEMID, ITEMNAME, COMPNAME, however, if I try to just remove those fields, access will say: "You tried to execute a query that doesn't include the specified expresssion 'ITEMID' as part of an aggregate function."

Any idea?

Hena

I'm sure someone has had the same trouble that I am.. come on, don't be shy, just tell me the solution to this

re: the original query -- use NOT EXISTS instead (if Access supports it, I think they do). The LEFT OUTER JOIN .. NULL thing is a MySQL workaround for not having subqueries.



Can you enclose your query in the 'code' tags instead of quote so it can be easily read? Also re-phrase your question -- I don't know what you want!