CODEWITHSUNDEEP
sqlcorrelated-subquery
user
user

Reputation: 253

A problem involving correlated subqueries

The problem at hand, - Get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i). Result set: model with the bigger number, model with the smaller number, speed, and RAM.

Select a.model,model, speed, hd from pc a
where (a.speed in (select speed from pc where model<>a.model and `
a.speed=speed) and a.hd in(select hd from pc  where a.model<>model and a.hd=hd))

I tried numerous ways, this is just one of them, the query resulted in identical models and every model had identical speed.

enter image description here

Here is database schema:

The database scheme consists of four tables: Product(maker, model, type) PC(code, model, speed, ram, hd, cd, price) Laptop(code, model, speed, ram, hd, screen, price) Printer(code, model, color, type, price)

Upvotes: 1

Views: 622

Answers (3)

Krutika Rao
Krutika Rao

Reputation: 11

This worked best for me. Hope this helps

select distinct (A.model), 
B.model,
A.speed,
A.ram from pc A 
join 
pc B on A.speed=B.speed and A.ram=B.ram where A.model>B.model

Upvotes: 1

Serg
Serg

Reputation: 22811

Try

select p1.model model1 
      ,p2.model model2
      ,p1.speed
      ,p1.hd 
from pc p1
join pc p2 on p1.speed = p2.speed 
    and p1.hd = p2.hd 
    and p1.model < p2.model

Upvotes: 3

Benoit F
Benoit F

Reputation: 499

I have this idea but im not sure it its gonna work, you tell me

select a.model, b.model, a.speed, a.hd from model a
left join (select model, speed, hd from model) b on a.speed = b.speed and a.hd = b.hd
where a.model <> b.model

Upvotes: 0

Related Questions