mysql this,MySQL同时选择This和That

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;