Power By 专否社区

MySQL Triggers. Need example answers

Go To StackoverFlow.com

4

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?

2012-04-03 21:51
by NoName
See http://nimal.info/blog/2007/intersection-and-set-difference-in-mysql-a-workaround-for-except - mellamokb 2012-04-03 21:54
Question: Do you want makers that have laptops but no pc's? Do you want makers that have pcs but no laptops - RolandoMySQLDBA 2012-04-03 22:05

1

select maker, type from product 
where type='laptop' and
maker not in (select maker from product where type = 'pc')
2012-04-03 21:59
by kasavbere
Ha! I wrote the same line concurrently :-) (THIS is the right approach - Victor Nițu 2012-04-03 22:05

2

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
2012-04-03 21:59
by Michael Fredrickson
This approach is a bit expensive as the subquery is run continuously - kasavbere 2012-04-03 22:00
@kasavbere Fixed... and +1 for your answer - Michael Fredrickson 2012-04-03 22:04

1

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

2012-04-03 21:56
by Victor Nițu
Actually this is utterly wrong, but I undeleted it for the reference: it will return only rubbish, because it would select PRODUCTS that aren't both PC and laptop at the same time. Again, wrong and it has nothing to do with the OP's question. See my other answer for a more competent approach - Victor Nițu 2012-04-03 22:03

0

SELECT `maker`, `type` FROM `product` WHERE `type` = 'laptop' AND `maker` NOT IN
                 (SELECT `maker`, `type` FROM `product` WHERE `type` = 'pc');
2012-04-03 22:00
by Victor Nițu

-1

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
    )
2012-04-03 21:54
by max_
Ads