Google Answers Logo
View Question
Q: Simple SQL Question ( Answered 5 out of 5 stars,   5 Comments )
Subject: Simple SQL Question
Category: Computers > Programming
Asked by: snep-ga
List Price: $2.00
Posted: 25 Aug 2005 17:40 PDT
Expires: 24 Sep 2005 17:40 PDT
Question ID: 560572
I am looking for a SQL Query for the below described problem:

There are two entities - Person and Employer. They have a many to many
relationship between them. To map such a relationship between Person
Table and Employer table, we have made use of a mapping table which
has 2 columns, one with person_id and other having Employer_ID. My
question is, I want a query which will return back a list of Persons
who don't work for any employer.

Clarification of Question by snep-ga on 28 Aug 2005 20:01 PDT
We plan to use either Oracle or MySql.
Subject: Re: Simple SQL Question
Answered By: hammer-ga on 29 Aug 2005 10:04 PDT
Rated:5 out of 5 stars

Depending on what version of which engine you end up with, there are a
couple of ways to do this. The simplest is to use a LEFT OUTER JOIN:

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS NULL;

You can also use NOT EXISTS or NOT IN in a subselect, again, depending
on what is available in your version.


Detailed usage on these techniques and more can be found at Sames Publishing.
Sams Publishing
MySQL SQL Syntax and Use

- Hammer

Search strategy
mySQL "outer join" unmatched
snep-ga rated this answer:5 out of 5 stars
thanks for the great answer hammer. Appreciate your help.

Subject: Re: Simple SQL Question
From: abdulmuqsith-ga on 26 Aug 2005 07:50 PDT
You can try following;

Persons who don't work for any employer don't appear in mapping table.
Thus the following query can work.

select person_name from person where person_id NOT IN (Select
person_id from map_table)
Subject: Re: Simple SQL Question
From: snep-ga on 26 Aug 2005 08:41 PDT
Hi Abdul,

      Thanks for your response. I am not sure if your query would work
since I feel "NOT IN" is not a valid parameter of SQL Select query. If
you find any reference for this online, please let me know.

Subject: Re: Simple SQL Question
From: meridius-ga on 26 Aug 2005 10:46 PDT
I back up Abdul on his statement, with this page from the MySQL reference docs:

(It would help to use the find feature on your browser, searching for
the string "NOT IN".) His query would work. Hopefully that helps.
Subject: Re: Simple SQL Question
From: hammer-ga on 26 Aug 2005 12:07 PDT

There are different "flavors" of SQL. Which version of which database
engine are you using? MySQL, SQL Server, Access, Oracle...?

- Hammer
Subject: Re: Simple SQL Question
From: klashk-ga on 26 Aug 2005 16:13 PDT
Depends on the DBMS in use, maybe try:

SELECT DISTINCT person_id FROM Persons
SELECT DISTINCT person_id FROM Emplotment

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy