Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Show Records From One Table not in another 1

Status
Not open for further replies.

likelylad

IS-IT--Management
Jul 4, 2002
388
GB
I have one table that contains the following

Table1
---------
Name |
---------
User1 |
User2 |
User3 |
User4 |
User5 |
---------

I also have a second table

Table2
------------------
Name | Skill |
------------------
User1 | Skill1 |
User1 | Skill2 |
User1 | Skill3 |
User2 | Skill2 |
User2 | Skill4 |
User3 | Skill1 |
------------------

What I would like to do is to show all records from Table1 that do not have the specified skill.

For example I want to show all users from table1 that do not have Skill3 in table2. The result should show the following.

---------
Name |
---------
User2 |
User3 |
User4 |
User5 |
---------

For example I want to show all users from table1 that do not have Skill2 in table2. The result should show the following.

---------
Name |
---------
User3 |
User4 |
User5 |
---------

I have tried a few joins but can't seem to get the results I want.

Thanking in advance for any help received.
 
how about

Code:
select name from table1 minus select name from table2 where skill = 'Skill3'

-----------------------------------------
I cannot be bought. Find leasing information at
 
when did mysql add the MINUS operator? i looked all over for it in the manual and i don't see it

i also did a search on the mysql.com site and can't find it



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
My mistake. I'm used to oracle. MINUS doesn't work in mysql 4.1. I don't know about newer versions.

-----------------------------------------
I cannot be bought. Find leasing information at
 
amazing that likelylad actually got it to work, then

or maybe he wasn't using mysql either

;-)
Code:
SELECT table1.name
  FROM table1
LEFT OUTER
  JOIN table2
    ON table2.name = table1.name
   AND table2.skill = 'Skill3'
 WHERE table2.name IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Sorry, the exact query didn't work but jaxtell sent me off in the right direction. The query I got to work is as follows:

Code:
Select Table1.Name from Table1 Where Table1.Name Not In ( select Table2.Name from Table2 Where Skill='skill3')
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top