General SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesGeneral SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old February 27th, 2004, 12:18 AM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 14
Select from table not in another table

I just spent all night trying to figure this out...

I have two tables which have two ID fields in common... I was trying to figure out how to select all the names from one table which aren't represented in the other table...

In oracle I remember using something along the lines of:
Code:
SELECT table1.id1 FROM table1
WHERE table1.id1 NOT IN (SELECT table2.foreign_id FROM table2);


And I just spent all evening trying to figure out how to do it in MySQL... I was so close to asking in the forums when I happened to stumble upon the answer...

After playing around with Left/right/straight joins and EXIST/NOT EXIST... I finally figured it out... hopefully this will benefit others, or likely myself when i'm blindly searching again four months down the road (selective memory, I know)... =)

Code:
SELECT table1.*
FROM table1
LEFT JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NULL;


My problem was that I was missing the WHERE clause... the most important part of the statement!
Again, hopefully this helps out...

Reply With Quote
  #2  
Old August 10th, 2004, 04:31 PM
busboy busboy is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 5 busboy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via AIM to busboy
I have the same problem as you.

Can you help me understand why this isn't working?

SELECT uID from users where NOT EXISTS (select author from testimonies);


The uID and author are in many cases, the same number. So I basicly want to see all of my users who have not taken the time to post a testimonial. I can't this to work for the life of me.

Reply With Quote
  #3  
Old August 10th, 2004, 04:51 PM
dhouston's Avatar
dhouston dhouston is offline
Contributing User
Dev Articles Beginner (1000 - 1499 posts)
 
Join Date: May 2003
Location: Tennessee
Posts: 1,355 dhouston User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 13
Send a message via ICQ to dhouston
What db are you using? I don't believe mysql supports subselects. Maybe a left outer join on users and testimonies, looking for nulls? You'll probably have to tweak it, but something along the following lines might be a step in the right direction:

Code:
SELECT u.uID, t.author LEFT OUTER JOIN users as u ON u.uID=t.author FROM testimonies as t WHERE author IS NULL
__________________
Please don't PM me asking for solutions outside the scope of a thread.
Keeping all responses in a thread stands to help others who come along later,
which is after all what this forum's all about.

Reply With Quote
  #4  
Old September 22nd, 2004, 12:35 PM
m0nkmaster m0nkmaster is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Sep 2004
Posts: 1 m0nkmaster User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Excellent tip MadCowDzz I had the exact same issue, trying to return all the records from a table which didn't appear in another table. I'd not thought of using a left join for the job! Using your example I got it working in seconds:

SELECT table1.email
FROM table1
LEFT JOIN table2
ON table1.email = table2.email
WHERE table2.email IS NULL;


My SQL is for Access.

Reply With Quote
  #5  
Old April 19th, 2008, 11:07 PM
klynnmi klynnmi is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2008
Posts: 2 klynnmi User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 16 m 25 sec
Reputation Power: 0
OK but....

Alright, i already found references on how to do that one but what happens if you only want to find items in the left table that do not appear in a subset of the right?

i.e. I have a link list between keywords and items. the link table, itemkeywords is basically two columns, itemID and keywordID - pointing to the unique keys in each of the two tables. (the combination is unique indexed together)

What if I want to see all the keywordID's that do 'not' show up in itemkeywords for a given itemID?

The method shown in this thread only shows me the keywordID's that do not show up in itemkeywords at all

Reply With Quote
  #6  
Old April 19th, 2008, 11:47 PM
klynnmi klynnmi is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2008
Posts: 2 klynnmi User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 16 m 25 sec
Reputation Power: 0
OK, I got it!

With a little more tinkering and a little guesswork (as well as some creative logic if I do say so myself - hehehe) I came up with the answer myself! woo hoo!

SELECT
DISTINCT k.keywordID,
k.keywordName
FROM
keywords AS k
WHERE (SELECT COUNT(ck.keywordID)
FROM categorykeywords AS ck
WHERE ck.categoryID=1
AND ck.keywordID = k.keywordID) = 0
ORDER BY
k.keywordID ASC

Yeah, i know my previous example talked about items, but I had three tables I'm linking to keywords and my items didn't have any data in it yet lol.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesGeneral SQL Development > Select from table not in another table


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 - 2014, Jelsoft Enterprises Ltd.

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