Reference a subquery with WHERE clause?

Hello,

I am trying to essentially figure out a way to do this:


SELECT *, (SELECT username FROM username_tracking WHERE wp_cart66_orders.trans_id = username_tracking.transID) username,
	(SELECT due_date FROM album_info WHERE wp_cart66_orders.trans_id = album_info.transID) due_date
	FROM wp_cart66_orders WHERE status='completed' AND username='blah'

Obviously it doesn’t work to ask WHERE username=‘blah’ because it is apart of a subquery, but how else can I get this information? I tried taking the subquery and putting it in the WHERE clause but that took like 16 seconds. Currently the code works fine if I leave out AND username=‘blah’ and gets the data in .1s

Thoughts? Appreciate the help!

Use a JOIN instead:


SELECT 
    wp_cart66_orders.*
  , username_tracking.username
  , (SELECT due_date FROM album_info WHERE wp_cart66_orders.trans_id = album_info.transID) due_date
FROM wp_cart66_orders 
INNER JOIN username_tracking 
ON wp_cart66_orders.trans_id = username_tracking.transID
WHERE status='completed' 
AND username='blah'

Thank you Guido. That worked great except that due_date is now showing NULL for some reason.

Are you sure your old query gave a result for due_date when the username was ‘blah’ ?

Ah ha. Thanks for helping me think this through. I see what I did…