MySQL compare each tuple to each other tuple in the same relation

Go To StackoverFlow.com

0

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.

2012-04-03 22:57
by NoName


1

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
2012-04-03 23:01
by Michael Fredrickson
So are creating two sub tables pc1 and pc2, and defining them as copies of pc, joining them on the basis that pc1.speed = pc2.speed and pc1.ram = pc2.ram. But what does the last AND pc1.model > pc2.model do exactly - NoName 2012-04-03 23:14
The query joins the table to itself. The first query won't join the exact same record to itself by saying that 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
Self-join will return all possible pairs, quite a lot if there are many models with the same speed and ram. I'd rather use group by with group_concat - piotrm 2012-04-03 23:32