Select from table not in another table

Dev Articles Community Forums Sponsor:

You don't need a fax machine to get faxes. Get a fax-to-email fax number from CallWave. Try it free.

February 26th, 2004, 11:18 PM

MadCowDzz

I'm Internet Famous

Dev Articles Frequenter (2500 - 2999 posts)

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

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...

August 10th, 2004, 03:31 PM

busboy

Registered User

Join Date: Jul 2004

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

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.

August 10th, 2004, 03:51 PM

dhouston

Contributing User

Dev Articles Beginner (1000 - 1499 posts)

Join Date: May 2003

Location: Tennessee

Posts: 1,355

Time spent in forums: < 1 sec
Reputation Power: 7

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.

September 22nd, 2004, 11:35 AM

m0nkmaster

Registered User

Join Date: Sep 2004

Posts: 1

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.

April 19th, 2008, 10:07 PM

klynnmi

Registered User

Join Date: Apr 2008

Posts: 2

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

April 19th, 2008, 10:47 PM

klynnmi

Registered User

Join Date: Apr 2008

Posts: 2

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.

Viewing: Dev Articles Community Forums > Databases > General SQL Development > Select from table not in another table

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	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

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!