CODEWITHSUNDEEP
mysql
Incognito
Incognito

Reputation: 455

MySql Select query combine results

Hi im having 2 tables customers and customer_items. There is now a customer_id in both tables in the 2nd table each customer can have multiple items so the table can be like this

id           |    item
----------------------------
1501         |    pillow
1501         |    blanket
1501         |    others
1502         |    pillow
1502         |    blanket
1502         |    others

now how can i select with a mysql query the customers that have both pillow and blanket

This is my last approach

select custlist.id FROM customers custlist LEFT JOIN customer_items custitems ON custitems.id=custlist.id WHERE (custitems.items='pillow' AND custitems.items='blanket') UNION ALL

Upvotes: 0

Views: 118

Answers (4)

Goutam Pal
Goutam Pal

Reputation: 1763

SELECT distinct customer_ID ,item FROM customer_items WHERE item in ('PILLOW', 'blanket') GROUP BY customer_ID HAVING COUNT(item) = 2

Upvotes: 0

Meherzad
Meherzad

Reputation: 8553

Try this query

SELECT 
   a.id 
FROM 
   customer_items a
INNER JOIN
   customer_items b
ON
   a.id= b.id and 
   a.item = 'PILLOW' AND 
   b.item='blanket'

if you want customer name then just join customer table. If customer have multiple pillow or blanket then add distinct

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

Slight variation of the above solution by JW:-

SELECT  a.customer_ID
FROM    customers a
INNER JOIN (SELECT customer_ID, item FROM customer_items WHERE item = 'pillow' GROUP BY customer_ID) PillowCheck
ON a.customer_ID = PillowCheck.customer_ID
INNER JOIN (SELECT customer_ID, item FROM customer_items WHERE item = 'blanket' GROUP BY customer_ID) BlanketCheck
ON a.customer_ID = PillowCheck.customer_ID

Upvotes: 1

John Woo
John Woo

Reputation: 263693

This is a Relational Division problem.

SELECT  a.customer_ID
FROM    customers a
        INNER JOIN customer_items b
            ON a.customer_ID = b.customer_ID
WHERE   b.item IN ('pillow', 'blanket')
GROUP   BY a.customer_ID
HAVING  COUNT(*) = 2

If item is not unique for every customer_ID, a DISTINCT keyword is need to count only unique records.

SELECT  a.customer_ID
FROM    customers a
        INNER JOIN customer_items b
            ON a.customer_ID = b.customer_ID
WHERE   b.item IN ('pillow', 'blanket')
GROUP   BY a.customer_ID
HAVING  COUNT(DISTINCT b.item) = 2

Upvotes: 1

Related Questions