CODEWITHSUNDEEP
mysqlsql
Rupak Banerjee
Rupak Banerjee

Reputation: 135

SQL query to select from same column but multiple values

This is my table.

id  customer        product 
1   Tizag           Pen 
4   Gerald Garner   19" LCD Screen  
5   Tizag           19" LCD Screen

I want to select the customer who has both "Pen" and '19" LCD Screen'. So, the result with be customer 'Tizag'.

How can I do this.

Thanks And Regards, Rupak Banerjee.

Upvotes: 2

Views: 43105

Answers (4)

Pratik Jain
Pratik Jain

Reputation: 1

select customer
from tablename
where product = 'Pen' AND
      customer IN (select customer from tablename where product = '19" LCD Screen');

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89661

Naively:

SELECT DISTINCT customer FROM tblname t1
WHERE EXISTS (SELECT * FROM tblname WHERE product = 'Pen' AND customer = t1.customer)
    AND EXISTS (SELECT * FROM tblname WHERE product = '19" LCD Screen' AND customer = t1.customer)

But I think in general, there's more to this kind of question and you need to give more details as to the environment and the range of parameters.

Please note, that I am not necessarily advocating this approach but I give it (and denote it as naive, meaning that it is very simple and takes very little into account in terms of table indexing) because:

  1. It's very portably ANSI - nothing special going on
  2. It is very clear (without joins) for a SQL beginner to parse and get understanding in a set-based manner
  3. It is obviously extensible by parameters (perhaps for dynamic SQL)

The drawbacks are that:

  1. Table scans, who knows how bad this execution plan is going to be
  2. Correlated subqueries, relating to problems with #1
  3. Verbosity, repetition
  4. Any SELECT * (even inside an EXISTS) is likely to raise an eyebrow

Upvotes: 5

kitti
kitti

Reputation: 14814

Try this:

SELECT customer FROM table t1
JOIN table t2 USING( customer )
WHERE t1.product = 'Pen'
AND t2.product = '19" LCD Screen'

However, the query needs to change if you change the number of products. There might be a better way to do this, but I think this will work.

Upvotes: 7

Teja
Teja

Reputation: 13534

SELECT CUSTOMER
FROM YOURTABLE yt1,YOURTABLE yt2
WHERE yt1.customer=yt2.customer
  AND yt1.product='Pen'
  AND yt2.product=''19" LCD Screen';

Upvotes: 1

Related Questions