Homework: no subquery in getting a value indirectly

Question

I'm having a hard time creating a query for smth that sounds like this: what is the name and color of the items that cost more than grey box detergent(2 attributes: color:grey, type:detergent).

NOTE: I'm NOT allowed to user subqueries or use the price as a constant.

using subqueries I managed to get this:

SELECT name, color
FROM items
WHERE cost > (SELECT price FROM items WHERE type='detergent' AND color='grey');

any ideas on how can I get the above query without using a subquery?

I'm running out of ideas...

Answer 1

You can use a join to self.

SELECT i1.name, i1.color
FROM items i1
INNER JOIN items i2 
  ON (i2.type = 'detergent' 
     AND i2.color = 'grey' 
     AND i1.cost > i2.cost)

Answer 2

This way:

SELECT distinct others.name, others.color
FROM items others
   Join items GreyDet
       On others.Price > greyDet.Price
WHERE others.type <> 'detergent'
    And others.Color <> 'grey'
    And greyDet.type = 'detergent'
    And greyDet.Color = 'grey';

NOTE: This solution restricts output to only rows that are not grey detergents. This is necessary if it is possible to have more than one grey detergent record, with different prices..

Answer 3

SELECT name, color FROM items t1 join (SELECT price FROM items WHERE type='detergent' AND color='grey') t2 on t1.price > t2.price;