selecting user info if 2 rows in mysql exist

[unistake]

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

[requinix]

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...

[unistake]

hi requinix,

 

Thanks for that. Actually there may well be multiple rows for each Code/SectorDate pair however its not guaranteed.

[Jacques1]

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