MySQL Forums
Forum List  »  Newbie

Need help with advanced Joins
Posted by: Mathijs Bronneberg
Date: November 01, 2009 04:59PM

Hi,

Srry about the title. I don't know how to describe the problem in a few words. So here goes:

I'm working on a page that shows me a list of orders from the 'cms_module_concerts_orders'-table.

The 'cms_module_concerts_orders'-table contains a 'customer_id'. This is the 'id' from the 'cms_module_feusers_users'-table.

The 'cms_module_feusers_users'-table 'id' is the 'userid' in the 'cms_module_feusers_users_properties'-table. This table contains all the customer info (name,adres,etc). BUT..the 'cms_module_feusers_users'-table is like this:

id | userid | title | data
1 | 231 | name | John
2 | 231 | age | 24
3 | 231 | gender| man
4 | 232 | name | Pete
5 | 232 | age | 34
6 | 232 | gender| man
(srry about the mark-up)

..get it? The name, age, gender is defined (or something) in an other table, where is states the length and types. This is new for me...(OO style maybe?)

Anyways, what I need is as follows. The order pages that shows me the order, also has to show me who placed the orders. The query so far is as follows, which shows the orders and the id of the customer, but I don't know how the get the other customer data in it:

SELECT co.id,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
GROUP BY co.id, co.at, co.status, co.transaction_id
ORDER BY co.id

I'm not able to change the whole DB setup.

Hopefully I explained everything, and hopefully someone can help me get this problem solved.

Options: ReplyQuote


Subject
Written By
Posted
Need help with advanced Joins
November 01, 2009 04:59PM


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.