You are viewing a single comment's thread. Return to all comments →
without using join
select w.id,wp.age,w.coins_needed,w.power from wands as w,wands_property as wp where w.code=wp.code and wp.is_evil=0 and w.coins_needed =(select min(wo.coins_needed) from wands as wo where wo.power=w.power and wo.code=w.code) order by power desc,age desc
Why can't we simply write order by coins_needed asc instead of sub query??
It seems your solution is still joining the two tables.
A solution without joining tables is as follows:
(SELECT age FROM Wands_Property WHERE code = w.code) AS age,
FROM Wands w
WHERE w.code IN
(SELECT code FROM Wands_Property WHERE is_evil = 0)
AND coins_needed =
(SELECT MIN(coins_needed) FROM Wands
WHERE code = w.code AND power = w.power)
ORDER BY power DESC, age DESC;
what is the use of code = w.code and power = w.power ?
why do we have to equate these same two columns?
w.coins_needed =(select min(wo.coins_needed) from wands as wo where wo.power=w.power and wo.code=w.code)
what is the use of w0.power=w.power and w0.code=w.code..?
I am highly confused.
Help me with this...
i am nt sure this works. also, you are using the functionality of join. jst not using the keyword.
select d.id,table2.age,table2.cn,table2.pw from (select table1.age age,min(table1.cn) cn,table1.pw pw from (select b.age age,a.coins_needed cn, a.power pw from wands a , wands_property b
where a.code=b.code and b.is_evil=0 )table1 group by table1.age,table1.pw)table2, wands_property c, wands d
where table2.age=c.age and c.code=d.code and table2.cn=d.coins_needed order by 4 desc, 2 desc ;
this is in oracle sql