CODEWITHSUNDEEP
phpmysql
Biju s
Biju s

Reputation: 430

mysql query to find shopname open in certain days

I need a mysql query to find shopname open in certain days,

My Shop_availability table saving shop_name & openday .

Table like this.

  shop_name  openday 
------------------------
    shop-A      Monday  
    shop-A      Tuesday
    shop-A      Wednesday
    shop-A      Thursday
    shop-A      Friday
    shop-B      Monday 
    shop-B      Tuesday

I want to get shop_name which open in Monday,Friday

Select * availability  WHERE openday IN('Monday','Friday')

above query not working,

Please help me , Thanks,

Upvotes: 0

Views: 39

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Close, but you want aggregation:

SELECT a.shop_name
FROM availability a
WHERE a.openday IN ('Monday', 'Friday')
GROUP BY a.shop_name
HAVING COUNT(*) = 2;

The HAVING clause is used to verify that there are two matches on the days (the number of days you are looking for). Presumably, there are no duplicates in your original data.

Upvotes: 2

juergen d
juergen d

Reputation: 204766

You need to group by the shop and then take only those having both opendays (count(*) = 2)

select shop_name 
from availability
where openday IN('Monday','Friday')
group by shop_name
having count(*) = 2

Upvotes: 1

Related Questions