SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    May 2002
    Location
    Louisville, KY
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Reference a subquery with WHERE clause?

    Hello,

    I am trying to essentially figure out a way to do this:
    Code:
    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!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Use a JOIN instead:

    Code:
    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'

  3. #3
    SitePoint Addict
    Join Date
    May 2002
    Location
    Louisville, KY
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Guido. That worked great except that due_date is now showing NULL for some reason.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Are you sure your old query gave a result for due_date when the username was 'blah' ?

  5. #5
    SitePoint Addict
    Join Date
    May 2002
    Location
    Louisville, KY
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah ha. Thanks for helping me think this through. I see what I did..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •