Relations: Product(maker, model, type) Laptop(Model, price, speed, ram, hd, screen) PC(model, price, speed, ram, hd) Printer(model,price, color, price)
I am trying to find pc's who share both the same speed and same ram.
What I have tried:
SELECT model FROM pc WHERE pc.speed = pc.speed AND pc.ram = pc.ram;
This is again (If you have seen my list of questions from today), that this is the wrong syntax.
I want to compare each tuple in pc to each other tuple in pc and create a new relation based on a pc's speed and ram sharing anothers.
SELECT pc1.model, pc2.model
FROM
pc pc1
JOIN pc pc2
ON pc1.speed = pc2.speed
AND pc1.ram = pc2.ram
AND pc1.model <> pc2.model
Note that this will return two rows for every match... A <-> B
and B <-> A
. If you want to prevent this, you can change your query to something like the following:
SELECT pc1.model, pc2.model
FROM
pc pc1
JOIN pc pc2
ON pc1.speed = pc2.speed
AND pc1.ram = pc2.ram
AND pc1.model > pc2.model
model
can't be equal. The second query will return only 1 record for a match by saying that the model
on the left side of the join must be greater than the model
on the right side of the join... It would work just as well with a <
.. - Michael Fredrickson 2012-04-03 23:16