How to SELECT same records

Question

How to SELECT same records

astralex (IS/IT--Management)

(OP)

12 Dec 09 13:57

Hi, how can I select all prodid in the table below which are availabe in both shopid 1 and 2? The answer should be Prodid 2

Table ProdsInShops

Shopid|Prodid
______|_______
1      |1
1      |2
1      |3
2      |4
2      |2

Thank you!

PHV (MIS) · Answer 1 · 2009-12-12 14:31:14

SELECT Prodid FROM ProdsInShops GROUP BY Prodid HAVING COUNT(*)>1

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

astralex (IS/IT--Management) · Answer 2 · 2009-12-12 14:34:11

RE: How to SELECT same records

astralex (IS/IT--Management)

(OP)

12 Dec 09 14:34

Great! Thank you for your help!

astralex (IS/IT--Management) · Answer 3 · 2009-12-12 14:40:03

Sorry, just one more question: how do I add that I want prodid only from shopid 1 and 2?

PHV (MIS) · Answer 4 · 2009-12-12 14:42:22

SELECT Prodid FROM ProdsInShops WHERE Shopid IN (1,2) GROUP BY Prodid HAVING COUNT(*)>1

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Come Join Us!

Posting Guidelines

How to SELECT same records