CODEWITHSUNDEEP
sqlsql-server-2005
Searock
Searock

Reputation: 6498

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.

Upvotes: 4

Views: 3354

Answers (6)

DVRK
DVRK

Reputation: 11

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

Upvotes: 1

syed soheb
syed soheb

Reputation: 21

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

Upvotes: 2

grace
grace

Reputation: 21

     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

Upvotes: 1

learning
learning

Reputation: 73

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).

Upvotes: 2

Ishtar
Ishtar

Reputation: 11662

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

Upvotes: 6

NPE
NPE

Reputation: 500773

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

Upvotes: 4

Related Questions