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

Answer 1

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"