Finding the pairs of PC models

I am trying to solve an sql exercise.

Here'开发者_运维知识库s the schema

PC

code     int  
model    varchar(50)  
speed    smallint  
ram      smallint  
hd       real  
cd       varchar(10)  
price    money  

The problem :

Find the pairs of PC models having similar speeds and RAM. As a result, each resulting pair is shown only once, i.e. (i, j) but not (j, i).

I have written a query but it displays (i, j) along with (j, i).

My query :

select  t1.model,t2.model,t1.speed,t1.ram from pc t1 , pc t2
where t1.speed = t2.speed and t1.ram = t2.ram and t1.model != t2.model

Output :

model   model   speed   ram
1121    1233    750     128
1232    1233    500     64
1232    1260    500     32
1233    1121    750     128
1233    1232    500     64
1260    1232    500     32

Required output:

model   model   speed   ram
1233    1121    750     128
1233    1232    500     64
1260    1232    500     32

So how do I avoid (j ,i) in my output?

Thanks.

---

The difference between your output and the required output are exactly the rows that have t1.model < t2.model. To remove those, just add another AND t1.model >= t2.model. But because you already require that t1.model != t2.model, the complete query is

select  t1.model,t2.model,t1.speed,t1.ram 
from pc t1 , pc t2 
where t1.speed = t2.speed and t1.ram = t2.ram and t1.model > t2.model

---

Assuming code is unique, you could restrict pairs to those where t1.code < t2.code.

---

SELECT distinct als1.model, als2.model, als1.speed, als1.ram FROM PC als1, PC als2 WHERE (als1.speed=als2.speed) AND (als1.ram=als2.ram) AND (als1.model>als2.model)

This will solve your problem of getting (j,i). A filter is needed to avoid duo result and so using the last filter (als1.model>als2.model), you can select whether you need (i, j) or (j,i).

---

SELECT DISTINCT t.model, l.model, t.speed, t.ram
FROM PC as t JOIN PC as l
ON t.speed = l.speed AND t.ram = l.ram
AND t.model>l.model

This should get correct solution on current and secondary database

---

     select pc1.model,pc2.model,pc1.speed,pc1.ram
     from pc pc1,pc pc2
     where pc1.speed=pc2.speed and pc1.ram= pc2.ram 
     group by pc1.model, pc2.model, pc1.speed, pc1.ram
     having pc1.model> pc2.model

---

The below code works good as we need to return the model column twice and get the speed and ram data and make sure we restrict the model data to not repeat, we need add the condition t1.model > t2.model:

SELECT t1.model,
       t2.model,
       t1.speed,
       t1.ram
FROM pc t1,
     pc t2
WHERE t1.speed = t2.speed
  AND t1.ram= t2.ram
GROUP BY t1.model,
         t2.model,
         t1.speed,
         t1.ram
HAVING t1.model > t2.model