CODEWITHSUNDEEP
mysqlsql
Spyromancer
Spyromancer

Reputation: 459

Select statements inside a WHERE IN() clause

Given these tables:

Product(maker, model)
Desktop(model)
Laptop(model)

I want to know the makers that make both a laptop and a desktop.

If I wanted to know if a maker makes two specific models I would do it like this:

SELECT p.maker FROM Product p
WHERE p.model IN ('model1', 'model2')
GROUP BY p.maker
HAVING COUNT(DISTINCT p.model) = 2;

In this case I don't need the specific models but I need to know that one model must be a laptop and the other one a desktop.

Should I do it using select statements inside the IN clause like this?

SELECT p.maker FROM Product p
WHERE p.model IN ((SELECT d.model FROM Desktop d WHERE d.model=p.model),
                  (SELECT l.model FROM Laptop d WHERE l.model=p.model))
GROUP BY p.maker
HAVING COUNT(DISTINCT p.model) = 2;

Or is there another better way to do it?

Edit: as suggested by HoneyBadger using EXISTS is a better way to do it:

SELECT p.maker FROM Product p
WHERE EXISTS(SELECT d.model FROM Desktop d WHERE d.model=p.model)
AND EXISTS(SELECT l.model FROM Laptop d WHERE l.model=p.model)
GROUP BY p.maker
HAVING COUNT(DISTINCT p.model)>1;

Upvotes: 0

Views: 88

Answers (2)

Roopesh
Roopesh

Reputation: 279

Exists is a method of getting the required information. The marked as answer reply is not the correct answer considering one maker could be making more than one desktop and more than one laptop too. It doesn't make it mandatory to have models from both desktop and laptop.

Here is one more way of getting that information. Try to check this also.

select maker, COUNT(DISTINCT(modeltype)) As ModelTypes FROM
(
select p.*, 'Desktop' As "ModelType" from Product p 
    inner join Desktop d on p.model = d.model
Union ALL
select p.*, 'Laptop' As "ModelType" from Product p 
    inner join Laptop l on p.model = l.model
) A
group by maker
having count(DISTINCT(modeltype)) = 2

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

SELECT p.maker FROM Product p
WHERE p.model IN (
      (SELECT d.model FROM Desktop d WHERE d.model=p.model 
       UNION
       SELECT l.model FROM Laptop d WHERE l.model=p.model
      )
    )
GROUP BY p.maker
HAVING COUNT(DISTINCT p.model) = 2;

I'd also look to refactor these into the same table, with a type field that can specify desktop vs laptop. I understand a laptop may have some different attributes (screen, camera/mic, bluetooth, etc)

Upvotes: 1

Related Questions