Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old July 7th, 2003, 02:24 AM
Hena Hena is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 28 Hena User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Smile 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

Reply With Quote
  #2  
Old July 7th, 2003, 02:49 AM
NoXcuz's Avatar
NoXcuz NoXcuz is offline
Wiking
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Sep 2000
Location: Sweden
Posts: 3,608 NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 8 h 44 m 46 sec
Reputation Power: 25
Something like
Code:
SELECT 
  t2.*
FROM
    table1
  LEFT JOIN
    table2
  ON 
    table1.id = table2.id
WHERE 
  table1.id IS NULL


//NoXcuz
__________________
UN*X is sexy!
who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep

Reply With Quote
  #3  
Old July 7th, 2003, 03:03 AM
Hena Hena is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 28 Hena User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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

Last edited by Hena : July 7th, 2003 at 03:10 AM.

Reply With Quote
  #4  
Old July 7th, 2003, 03:31 AM
NoXcuz's Avatar
NoXcuz NoXcuz is offline
Wiking
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Sep 2000
Location: Sweden
Posts: 3,608 NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 8 h 44 m 46 sec
Reputation Power: 25
Sorry, I mixed things up a bit...
It should be something like this instead (swap table1 and table2 in the FROM part)
Code:
SELECT 
  t2.*
FROM
    table2
  LEFT JOIN
    table1
  ON 
    table1.id = table2.id
WHERE 
  table1.id IS NULL


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

Reply With Quote
  #5  
Old July 7th, 2003, 03:36 AM
Hena Hena is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 28 Hena User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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 )

Reply With Quote
  #6  
Old July 8th, 2003, 02:27 AM
Hena Hena is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 28 Hena User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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:
Quote:
SELECT EXCEL_TEMP_PUR.*
FROM EXCEL_TEMP_PUR LEFT JOIN EXCEL_118 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)
WHERE (EXCEL_118.PROJID IS NULL) AND (EXCEL_118.WORK_NUMBER IS NULL) AND (EXCEL_118.LAJI IS NULL) AND (EXCEL_118.ORDERID IS NULL) AND (EXCEL_118.ORDER_ROWNUM IS NULL);

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

Reply With Quote
  #7  
Old July 8th, 2003, 03:24 AM
NoXcuz's Avatar
NoXcuz NoXcuz is offline
Wiking
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Sep 2000
Location: Sweden
Posts: 3,608 NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level)NoXcuz User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 8 h 44 m 46 sec
Reputation Power: 25
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:
Code:
SELECT 
  EXCEL_TEMP_PUR.PROJID, 
  COUNT(EXCEL_TEMP_PUR.PROJID) AS number 
FROM 
    EXCEL_TEMP_PUR 
  LEFT JOIN 
    EXCEL_118 
  USING PROJID 
WHERE 
  EXCEL_118.PROJID IS NULL 
GROUP BY
  EXCEL_TEMP_PUR.PROJID
HAVING 
  number >= 2


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

Reply With Quote
  #8  
Old July 8th, 2003, 03:58 AM
Hena Hena is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 28 Hena User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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?

Quote:
INSERT INTO EXCEL_TEMP_PUR ( PROJID, WORK_NUMBER, LAJI, ORDERID, ORDER_ROWNUM, ITEMID, ITEMNAME, COMPANYNAME, SIDOTUT )
SELECT PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, Left(DLG_ITEM.ITEM_TYPE_NAME,2) AS LAJI, PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.ORDER_ROWNUM, PUR_ORDERBATCH.ITEMID, PUR_ORDERBATCH.ITEMNAME, PUR_ORDERBATCH.COMPNAME, Sum(PUR_ORDERBATCH.AMOUNT_BATCH) AS SumOfAMOUNT_BATCH
FROM PUR_ORDERBATCH, DLG_ITEM
WHERE (((PUR_ORDERBATCH.PROJID) Is Not Null) AND ((DLG_ITEM.ITEMID)=[PUR_ORDERBATCH].[ITEMID]))
GROUP BY PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, Left(DLG_ITEM.ITEM_TYPE_NAME,2), PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.ORDER_ROWNUM, PUR_ORDERBATCH.ITEMID, PUR_ORDERBATCH.ITEMNAME, PUR_ORDERBATCH.COMPNAME;


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

Reply With Quote
  #9  
Old July 9th, 2003, 12:30 AM
Hena Hena is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 28 Hena User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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

Reply With Quote
  #10  
Old July 9th, 2003, 06:51 AM
MattR MattR is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2001
Location: High above the mucky-muck (Columbus, OH)
Posts: 266 MattR User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 12
Send a message via ICQ to MattR
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.

Code:
SELECT *
  FROM excel_temp_pur a
 WHERE NOT EXISTS( SELECT *
                     FROM excel_118 b
                    WHERE a.projid = b.projid )


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!

Reply With Quote
  #11  
Old January 5th, 2013, 11:54 AM
blackfyz blackfyz is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Location: sri lanka
Posts: 1 blackfyz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 m 39 sec
Reputation Power: 0
Send a message via Skype to blackfyz
Quote:
Originally Posted by NoXcuz
Sorry, I mixed things up a bit...
It should be something like this instead (swap table1 and table2 in the FROM part)
Code:
SELECT 
  t2.*
FROM
    table2
  LEFT JOIN
    table1
  ON 
    table1.id = table2.id
WHERE 
  table1.id IS NULL


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


you helped me BIG TIME!! tanku sooo much!!

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > Selecting only values from one table that do NOT exist in another?

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap