Simple Join Query Driving me nuts

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
johnc71
Forum Newbie
Posts: 10
Joined: Tue Jun 24, 2008 2:19 am

Simple Join Query Driving me nuts

Post by johnc71 »

I have two tables, students and europeans. I would like to get a list of students that ARE NOT europeans.

I can get a list of the ones that ARE europeans by doing the following:
SELECT * FROM STUDENTS a, EUROPEANS b where a.student_id = b.student_id

but how do I get the list of the ones that ARE NOT? It is 2:40AM and my brain is not working anymore.

Code: Select all

STUDENTS
student_id | name
1 | Mary
2 | John
3 | Adam
4 | Mark
 
EUROPEANS
id | student_id
1  |  2
2  |  4
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Simple Join Query Driving me nuts

Post by Eran »

Code: Select all

SELECT * FROM students WHERE student_id NOT IN (
     SELECT student_id FROM europeans
)
Why use a table for each continent though? it will be much more efficient (not to mention normalized) to have a continent identifier field in the students table, which can be joined against a continents table. Also, using capital letters for table is bad practice in my opinion, as conventions say that MySQL reserved words should be capital and it makes it hard to distinguish between them by sight only.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Simple Join Query Driving me nuts

Post by VladSun »

Or
[sql]SELECT     STUDENTS.* FROM     STUDENTS LEFT JOIN    EUROPEANS ON STUDENTS.student_id = EUROPEANS.student_idWHERE     EUROPEANS.student_id IS NULL[/sql]

I haven't checked which query (pytrin's or mine) is faster - make some tests.
Also, I would advice you not to use coma operator instead of JOIN keyword.
There are 10 types of people in this world, those who understand binary and those who don't
johnc71
Forum Newbie
Posts: 10
Joined: Tue Jun 24, 2008 2:19 am

Re: Simple Join Query Driving me nuts

Post by johnc71 »

Thanks guys!
Post Reply