sql query without subquery

Question

I need to rewrite this query and I'm not allowed to use a subquery. I need to select the name and color of the parts that are heavier than the wheel.

SELECT name, color
FROM parts
WHERE weight > (SELECT weight FROM parts WHERE name="wheel");

This is the table:

PARTS

ID    NAME    COLOR     WEIGHT    
1     wheel   black     100
2     tire    black     50
3     gear    red       20

Thanks in advance

@Ben probably - it does appear to be from a standard demo database — David LeBauer, Mar 22 '12 at 23:35
Just a thought, MySQL views do not permit you to use subqueries in the view statement so maybe he needs a view? — Matt Dodge, Mar 22 '12 at 23:51

score 2 · Accepted Answer · answered Mar 22 '12 at 23:25

2

Join it with itself

SELECT parts_a.name, parts_a.color 
FROM parts parts_a, parts parts_b
WHERE parts_a.weight > parts_b.weight
   AND parts_b.name = "wheel"

answered Mar 22 '12 at 23:25

Matt Dodge

10,833
7
38
58

score 0 · Answer 2 · answered Mar 22 '12 at 23:38

0

You can do with join.

SELECT a.name, a.color 
FROM parts as a left join parts as b on a.ID=b.ID
WHERE a.weight > b.weight
AND b.name = "wheel"

answered Mar 22 '12 at 23:38

won't that only return the wheel row? – Matt Dodge Mar 22 '12 at 23:51

score -1 · Answer 3 · answered Oct 11 '16 at 00:07

-1

I should write down this query without using subqueries SELECT C.pid FROM Catalog C, Suppliers S WHERE S.sname = ‘Yosemite Sham’ AND C.sid = S.sid AND C.cost ≥ ALL (Select C2.cost FROM Catalog C2, Suppliers S2 WHERE S2.sname = ‘Yosemite Sham’ AND C2.sid = S2.sid)

answered Oct 11 '16 at 00:07

davis

29
2

I'm not sure that this is a particularly [good answer](http://stackoverflow.com/help/how-to-answer) . – Alastair Brown Oct 11 '16 at 00:31

sql query without subquery

3 Answers3