Results 1 to 2 of 2

Thread: Left Joins

  1. #1
    Join Date
    Jun 2009
    Posts
    33

    Unanswered: Left Joins

    Can't seem to get this working.

    Code:
    $query1 = "SELECT jos_users.email
    				, jos_users.id
    				, jos_users.usertype
    				, jos_users.block
    				, jos_comprofiler.firstname
    				, jos_comprofiler.lastname
    				, jos_comprofiler.user_id
    				, jos_comprofiler.approved
    				, jos_comprofiler.confirmed
    				, jos_comprofiler.banned
    				, jos_comprofiler.cb_address1
    				, jos_comprofiler.cb_city
    				, jos_comprofiler.cb_state
    				, jos_comprofiler.cb_zip
    				, jos_comprofiler.phone
    				, jos_comprofiler.fax
    				, jos_comprofiler.cb_country
    				, jos_comprofiler.cb_primaryresponsibility
    				, jos_cbsubs_subscriptions.user_id AS sub_ID
    				, jos_cbsubs_subscriptions.plan_id
    				, jos_cbsubs_subscriptions.status
    				, jos_cbsubs_subscriptions.expiry_date
    				, jos_cbsubs_subscriptions.subscription_date
    				, jos_cbsubs_subscriptions.last_renewed_date
    				, jos_cbsubs_payment_baskets.payment_status
    				, jos_cbsubs_payment_baskets.user_id
    			FROM jos_users 
    		INNER
    			JOIN jos_comprofiler
    				ON jos_comprofiler.user_id = jos_users.id
    		INNER
    			JOIN jos_cbsubs_subscriptions
    				ON jos_cbsubs_subscriptions.user_id = jos_users.id
    					WHERE 
    				jos_cbsubs_subscriptions.plan_id = 4 AND (jos_cbsubs_subscriptions.status = 'A' OR jos_cbsubs_subscriptions.status = 'C')	
    		LEFT 
    			JOIN jos_cbsubs_payment_baskets 
    				ON jos_cbsubs_payment_baskets.user_id = jos_users.id 
    			";
    Left Join will return an entry even if the jos_cbsubs_payment_baskets.user_id attribute is missing correct?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is correct

    however, you cannot have that WHERE clause in the middle of your FROM clause -- the WHERE clause must come after all the joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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