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
modelcan't be equal. The second query will return only 1 record for a match by saying that the
modelon the left side of the join must be greater than the
modelon the right side of the join... It would work just as well with a
<.. - Michael Fredrickson 2012-04-03 23:16