Need help with advanced Joins
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.
Subject
Written By
Posted
Need help with advanced Joins
November 01, 2009 04:59PM
November 01, 2009 05:41PM
November 01, 2009 05:50PM
November 01, 2009 07:52PM
November 02, 2009 03:46AM
November 02, 2009 03:57AM
November 02, 2009 04:04AM
November 02, 2009 04:28AM
November 02, 2009 05:00AM
November 02, 2009 05:10AM
November 02, 2009 05:24AM