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] 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... 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"... ;-) 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? 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 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 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. 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
Archived
This topic is now archived and is closed to further replies.