CODEWITHSUNDEEP
phpmysql
user3501676
user3501676

Reputation: 487

mysql query where conditions

I have a mysql database, here are the columns;

date

computerlab

period

Only 2 possible data values go into the computerlab column, A101 or A102

However, there can be duplicate rows of data but with different computerlab data

For example,

date = 3-15-16 computerlab = A101 period = 1

date = 3-15-16 computerlab = A102 period = 1

My webpage gets input from the user receiving several periods 1,3,5

This is what I need:

If period 1 or 3 or 5 contains both A101 and A102 then return that date

I would think this would work but it doesn't

mysql_query("SELECT date FROM reservations WHERE period IN ($periods) AND lab in ('A101','A102') GROUP BY date HAVING COUNT(*) = 2");

Upvotes: 3

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

I think you need to group by both period and date to get what you want:

SELECT date, period
FROM reservations
WHERE period IN ($periods) AND lab in ('A101', 'A102')
GROUP BY period, date
HAVING COUNT(*) = 2;

This will check that a given period has both labs. You were checking that the set of three periods had exactly two entries.

Hmmm, I realize that you might intend to check if the three periods had both labs assigned, in any combination. If so then use count(distinct):

SELECT date
FROM reservations
WHERE period IN ($periods) AND lab in ('A101', 'A102')
GROUP BY date
HAVING COUNT(DISTINCT lab) = 2;

Upvotes: 3

Related Questions