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.

Snep-ga,

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.

SELECT i1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.i1 = t2.i2);

Detailed usage on these techniques and more can be found at Sames Publishing.
Sams Publishing
MySQL SQL Syntax and Use
http://www.samspublishing.com/articles/article.asp?p=30875&seqNum=5&rl=1

- Hammer


Search strategy
----------------
mySQL "outer join" unmatched

thanks for the great answer hammer. Appreciate your help.

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)

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.

Thanks.

I back up Abdul on his statement, with this page from the MySQL reference docs:

http://dev.mysql.com/doc/mysql/en/comparison-operators.html

(It would help to use the find feature on your browser, searching for
the string "NOT IN".) His query would work. Hopefully that helps.

snep,

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

- Hammer

Depends on the DBMS in use, maybe try:

SELECT DISTINCT person_id FROM Persons
MINUS
SELECT DISTINCT person_id FROM Emplotment