CODEWITHSUNDEEP
mysqlsqldatabase
whitwhoa
whitwhoa

Reputation: 2489

MySQL filter on column value within multiple rows?

Suppose I have these two tables:

|Customers     |          |Purchases      |
----------------          -----------------
|CusID         |-----     |PurchaseID     |
|CusName       |     |---<|CusID          |
|CusAge        |          |ItemName       |
|CusDateAdded  |          |DatePurchased  |

I am needing to filter my result set by multiple ItemNames. Say I want to return all Customers who are between the ages of 18 and 24 who purchased a 'camera' AND a 'phone'. I can run the following query to obtain all records for customers between the age range:

SELECT CusID
FROM Customers AS C
JOIN Purchases AS P
ON C.CusID = P.CusID
WHERE C.CusAge BETWEEN 18 AND 24

However when it comes time to filter on the ItemName column in Purchases table how does one filter on multiple rows? Supposing it is possible without multiple queries?

Upvotes: 1

Views: 1687

Answers (2)

Rafay
Rafay

Reputation: 601

I believe this will answer your question: SQL FIDDLE

SELECT C.CustID,C.CustName,P.ItemName
FROM Customers AS C
JOIN Purchases AS P ON C.CustID = P.CustID
GROUP BY C.CustID,C.CustName,P.ItemName
HAVING P.ItemName IN ('camera','phone')

Upvotes: 0

juergen d
juergen d

Reputation: 204746

SELECT C.CusID
FROM Customers AS C
JOIN Purchases AS P ON C.CusID = P.CusID
WHERE C.CusAge BETWEEN 18 AND 24
AND ItemName IN ('camera','phone')
GROUP BY C.CusID
HAVING count(distinct ItemName) = 2

Group by the customer and return only those having both items.

Upvotes: 2

Related Questions