The tables look like these:
Table: Shops
ShopCode ShopName
-------- --------
A Aladdin
B Backstreet
C Clerk's Store
D Debs Tool
Table: Sale
ShopCode Product
-------- -------
A Hammer
A Thermometer
A Compass
B Eraser
B Hammer
C Thermometer
C Hammer
D Thermometer
Find the name of the shops which sells BOTH Hammer and Thermometer. The result table would be
ShopName
--------------
Aladdin
Clerk's Store
I thought the following query will work, but its returning empty set
mysql> SELECT Shops.ShopName FROM Shops
-> JOIN Sale ON Shops.ShopCode=Sale.ShopCode
-> WHERE Sale.Product='Hammer' AND Sale.Product='Thermometer'
-> GROUP BY Shops.ShopCode;
Also tried with OR instead of AND, but not working (returning all the shops). What might be a possible solution?
Just to make it little bit clear, I want to select the shops that have both the items(hammer and thermo), even though shop B sells hammer and D sells Thermometer, they will not be included. Only A and C which is selling both the items should be on the result
解决方案
There are two fairly straight forward options.
You can join twice with the sale table, once per item. If you skip the DISTINCT, you may get duplicate values if the store sells more than one hammer or thermometer.
SELECT DISTINCT s.shopname
FROM shops s
JOIN sale s1 ON s.shopcode = s1.shopcode AND s1.product='hammer'
JOIN sale s2 ON s.shopcode = s2.shopcode AND s2.product='thermometer';
...or you can find all matches with hammer or thermometer and count how many distinct values there are. If there are two possible values and you get both, you're set.
SELECT s.shopname
FROM shops s
JOIN sale s1 ON s.shopcode = s1.shopcode
WHERE s1.product IN('hammer','thermometer')
GROUP BY s.shopname
HAVING COUNT(DISTINCT s1.product)=2;