CODEWITHSUNDEEP
mysql
user1311286
user1311286

Reputation:

MySQL Triggers. Need example answers

I am trying to find the makers of products who only make laptops and not PC's

Relations:
Product(maker, model, type)
Laptop(Model, price, speed, ram, hd, screen)
PC(model, price, speed, ram, hd)
Printer(model,price, color, price)

What I have tried

 (SELECT maker, type 
 FROM product WHERE type = 'laptop') 
 DIFFERENCE 
 (SELECT maker, type 
 FROM product WHERE type = 'pc');

I take it there is no difference operation in MySQL?

Upvotes: 4

Views: 219

Answers (5)

max_
max_

Reputation: 24521

SELECT p.maker, p.type 
    FROM product p 
    WHERE p.type = 'laptop' 
    AND NOT EXISTS ( 
        SELECT p2.maker, p2.type 
        FROM table p2 
        WHERE p2.type = 'pc' 
        AND p2.maker = p1.maker
    )

Upvotes: -1

Victor Nițu
Victor Nițu

Reputation: 1505

SELECT `maker`, `type` FROM `product` WHERE `type` = 'laptop' AND `maker` NOT IN
                 (SELECT `maker`, `type` FROM `product` WHERE `type` = 'pc');

Upvotes: 0

kasavbere
kasavbere

Reputation: 6003

select maker, type from product 
where type='laptop' and
maker not in (select maker from product where type = 'pc')

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

SELECT 
    p1.maker, 
    p1.type
FROM 
    product p1 
    LEFT JOIN product p2 ON p2.maker = p1.maker AND p2.type = 'pc'
WHERE 
    p1.type = 'laptop'
    p2.maker IS NULL

Upvotes: 2

Victor Nițu
Victor Nițu

Reputation: 1505

SELECT maker, type FROM product WHERE type = 'laptop' AND type != 'pc';

Upvotes: 1

Related Questions