unistake Posted January 31, 2016 Share Posted January 31, 2016 Hi all, I am trying to make a list of users where two rows in a mysql database exist. My attempt so far: "SELECT * FROM mail_list INNER JOIN rosters ON mail_list.Code = rosters.Code WHERE rosters.SectorDate = '2016-01-04' AND EXISTS (SELECT * FROM rosters WHERE rosters.SectorDate = '2016-01-24' IS NOT NULL)" So basically, I want to select all the users information from mail_list table only if in the rosters table the user has a row that exists with the date 2016-01-04 and a second row 2016-01-24. I have tried several types of EXIST statements as above but no luck so far :/ Please help!! Thanks Quote Link to comment Share on other sites More sharing options...
requinix Posted January 31, 2016 Share Posted January 31, 2016 I, preferring to avoid subqueries whenever possible, would do two JOINs SELECT ml.* FROM mail_list ml JOIN rosters r1 ON ml.Code = r1.Code AND r1.SectorDate = '2016-01-04' JOIN rosters r2 ON ml.Code = r2.Code AND r2.SectorDate = '2016-01-24' unless there are multiple rows in rosters for each Code/SectorDate pair... 1 Quote Link to comment Share on other sites More sharing options...
unistake Posted January 31, 2016 Author Share Posted January 31, 2016 hi requinix, Thanks for that. Actually there may well be multiple rows for each Code/SectorDate pair however its not guaranteed. Quote Link to comment Share on other sites More sharing options...
Solution Jacques1 Posted January 31, 2016 Solution Share Posted January 31, 2016 (edited) SELECT -- select concrete columns, not just "*" FROM mail_list JOIN rosters USING (Code) WHERE rosters.SectorDate IN ('2016-01-04', '2016-01-24') GROUP BY -- repeat all the columns from the SELECT part HAVING COUNT(DISTINCT rosters.SectorDate) = 2 ; Edited January 31, 2016 by Jacques1 Quote Link to comment 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.