MySQL Forums
Forum List  »  Newbie

Re: Need help with advanced Joins
Posted by: laptop alias
Date: November 01, 2009 07:52PM

Try something like this:
SELECT co.id
     , x.*
     , co.at
     , co.status
     , co.transaction_id
     , SUM(cot.number_of_tickets) AS number_of_tickets
     , SUM(cot.value) AS total_value,co.customer_id
  FROM cms_module_concerts_orders co
  LEFT 
  JOIN cms_module_concerts_orders_tickets cot 
    ON co.id = cot.order_id
  LEFT 
  JOIN cms_module_feusers_users cus 
    ON co.id = cus.id
  
  LEFT 
  JOIN 
     (
       SELECT userid
            , MAX(CASE WHEN title = 'name' THEN data END)name 
            , MAX(CASE WHEN title = 'age' THEN data END)age 
            , MAX(CASE WHEN title = 'gender' THEN data END)gender 
         FROM eav GROUP BY userid
     ) x
    ON x.userid = co.id
 GROUP 
    BY co.id
     , co.at
     , co.status
     , co.transaction_id
 ORDER 
    BY co.id;



Edited 1 time(s). Last edit at 11/02/2009 03:57AM by laptop alias.

Options: ReplyQuote


Subject
Written By
Posted
Re: Need help with advanced Joins
November 01, 2009 07:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.