ORDER BY id IN Subquery

Question

I have a query like this:

SELECT i.* 
FROM items i 
WHERE i.id 
IN (
    SELECT c.item_id 
    FROM cart c 
    WHERE c.sessID=MY_SESSION_ID
   ) 

It's working beautifully, but I need to sort items from the cart by date of purchase (cart.id) DESC. I don't want sort in PHP. How can I sort by cart.id?

I tried:

SELECT i.* 
FROM items i 
WHERE i.id 
IN (
    SELECT c.item_id 
    FROM cart c 
    WHERE c.sessID=MY_SESSION_ID
   ) 
ORDER BY c.id 

But it did not sort correctly.

Answer 1

Change your Sub query to Inner Join. Sub query will not allow to you refer the columns outside of sub query. So change it to Inner join

SELECT i.*
FROM   items i
       JOIN (SELECT item_id,
                    id
             FROM   cart) C
         ON i.id = c.item_id
            AND c.sessID = MY_SESSION_ID
ORDER  BY c.id Desc

or use this.

SELECT i.*
FROM   items i
       JOIN cart C
         ON i.id = c.item_id
            AND c.sessID = MY_SESSION_ID
ORDER  BY c.id Desc