mysql multiple values in 2 rows

Question

I have a table with those fields: phID, ProductID

The phID is the pharmacy id and the ProductID is the product id.

Example:

phID    ProductID
-----------------
1001    9
1001    10
1001    11
1004    9
1004    12
1004    14
1004    11

The query that I want is so I can get all the phID that has the same product. I have this query:

SELECT phID, ProductID
FROM ph_pd
WHERE ProductID IN (9,10,11)

I want the results to be

or just

1001
1004

sqlfiddle.com/#!2/021cd/5 I made this from LolCoder answer and i think is ok — Ind F. Ashiku
– Ind F. Ashiku, Commented Mar 12, 2013 at 9:59

Akash KC · Accepted Answer · 2013-03-12 09:09:50Z

1

If you want to get distinct phID which are fall under the certain productID, you have to use DISTINCT...

  SELECT distinct phID FROM ph_pd WHERE ProductID IN (9,10,11)

answered Mar 12, 2013 at 9:09

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

faissal · Accepted Answer · 2013-03-12 09:21:36Z

1

You can use :

SELECT p.phID , p.ProductID FROM ph_pd p
WHERE (select count(*) from ph_pd where ProductID = p.ProductID) >= 2
ORDER BY ProductID ;

answered Mar 12, 2013 at 9:21

faissal

2615 silver badges20 bronze badges

John Woo · Accepted Answer · 2013-03-12 09:16:43Z

0

SELECT  phID
FROM    tableName
WHERE   productID IN (9,10,11)
GROUP   BY phID
HAVING  COUNT(DISTINCT productID) = 2 -- since there are two stores

answered Mar 12, 2013 at 9:10

John Woo

265k70 gold badges509 silver badges500 bronze badges