johnc71 Posted April 17, 2009 Share Posted April 17, 2009 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. [pre] students +--------------------------+-----------------+ | student_id | student_name | +--------------------------+-----------------+ | 1 | Mark | | 2 | Anna | | 3 | Milos | | 4 | Debby | | 5 | Stan | | 6 | Natasha | +--------------------------+-----------------+ european +--------------------------+-----------------+ | id | student_id | +--------------------------+-----------------+ | 1 | 2 | | 2 | 3 | | 3 | 6 | +--------------------------+-----------------+ [/pre] Quote Link to comment https://forums.phpfreaks.com/topic/154466-solved-simple-join-query-driving-me-nuts/ Share on other sites More sharing options...
Mchl Posted April 17, 2009 Share Posted April 17, 2009 SELECT * FROM students WHERE student_id NOT IN (SELECT student_id FROM europeans) This should work. However I have some concerns regarding this design... Quote Link to comment https://forums.phpfreaks.com/topic/154466-solved-simple-join-query-driving-me-nuts/#findComment-812177 Share on other sites More sharing options...
fenway Posted April 17, 2009 Share Posted April 17, 2009 Just like I have concerns with "NOT IN"... ;-) Quote Link to comment https://forums.phpfreaks.com/topic/154466-solved-simple-join-query-driving-me-nuts/#findComment-812287 Share on other sites More sharing options...
Mchl Posted April 17, 2009 Share Posted April 17, 2009 What's your concern about it? Quote Link to comment https://forums.phpfreaks.com/topic/154466-solved-simple-join-query-driving-me-nuts/#findComment-812350 Share on other sites More sharing options...
mtoynbee Posted April 17, 2009 Share Posted April 17, 2009 SELECT * FROM students AS a LEFT OUTER JOIN europeans AS b ON a.student_id = b.student_id WHERE b.student_id IS NULL Quote Link to comment https://forums.phpfreaks.com/topic/154466-solved-simple-join-query-driving-me-nuts/#findComment-812351 Share on other sites More sharing options...
Mchl Posted April 17, 2009 Share Posted April 17, 2009 Nice Quote Link to comment https://forums.phpfreaks.com/topic/154466-solved-simple-join-query-driving-me-nuts/#findComment-812354 Share on other sites More sharing options...
fenway Posted April 17, 2009 Share Posted April 17, 2009 What's your concern about it? Performance nightmare. Quote Link to comment https://forums.phpfreaks.com/topic/154466-solved-simple-join-query-driving-me-nuts/#findComment-812526 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.