Hittade en sida där man ska lösa diverse sql-problem. Riktigt kul. Hittade dock en fråga jag inte lyckas lösa. Lite trött nu, men något i stil med följande... En annan variant... Tack för snabba svar. Tyvärr var ingen av dessa korrekt.One-to-many
Tabeller:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
Problem:
Find out the makers that sale PCs but not laptops.
Denna kod skrev jag, men den godkändes ej (gav dock rätt resultat):
<code>
Select Distinct Product.Maker From Product
Left Join Laptop On Product.Model = Laptop.Model
Inner Join PC On Product.Model = PC.Model
Where Product.Maker Not In (Select Distinct Product.Maker From Product Inner Join Laptop On Product.Model = Laptop.Model)
</code>
Är det någon vänlig själ som kan komma på en alternativ lösning?Sv: One-to-many
<code>
SELECT DISTINCT maker FROM Product
WHERE NOT maker IN (SELECT maker FROM Product LEFT JOIN Laptop ON Laptop.model = Product.model)
AND maker IN (SELECT maker FROM Product LEFT JOIN PC ON PC.model = Product.model)
</code>Sv:One-to-many
<code>
SELECT DISTINCT maker FROM Product
LEFT JOIN PC ON PC.model = Product.model
WHERE maker NOT IN (SELECT maker FROM Product LEFT JOIN Laptop ON Laptop.model = Product.model)
</code>Sv: One-to-many
Ni kan testa på http://www.sql-ex.ru/
Fåga 8 i select.Sv:One-to-many
select distinct maker from product
where maker not in (select maker from product where type like 'laptop')
and type like 'PC'