Complex SQL query :: display data inline from multiply tables
Hi i have a proble in finding solution.
I have 3 tables:
- Customer
- customer_id
- customer_name
- Relation
- subscription_id
- customer_id
- Subscriptions
One customer can have relation with many subscriptions and i want to display data like so:
customer_id, customer_name, subscription_first, subscription_second, subscription_n
...all in one line.
Can any one help me :?
Ok, first of all, thanks for help :)
i did something like this and its working :)
SELECT `main_table`.*, `customer_lastname_table`.`value` AS `customer_lastname`, `customer_firstname_table`.`value` AS `customer_firstname`, IF(main_table.customer_id = 0, 1, 2) AS `type`, `store`.`group_id`, `store`.`website_id`, `subscription_table_one`.`subscription_code`, `subscription_table_two`.`subscription_code` FROM `newsletter_subscriber` AS `main_table`
LEFT JOIN `customer_entity_varchar` AS `customer_lastname_table` ON customer_lastname_table.entity_id=main_table.customer_id
AND customer_lastname_table.attribute_id = 7
LEFT JOIN `customer_entity_varchar` AS `customer_firstname_table` ON customer_firstname_table.entity_id=main_table.customer_id
AND customer_firstname_table.attribute_id = 5
INNER JOIN `core_store` AS `store` ON store.store_id = main_开发者_JAVA技巧table.store_id
LEFT JOIN `b_newsletter_relations` AS `relation_table` ON relation_table.customer_id=main_table.customer_id
LEFT JOIN `b_newsletter_subscriptions` AS `subscription_table_one` ON subscription_table_one.subscription_id=relation_table.subscription_id
LEFT JOIN `b_newsletter_subscriptions` AS `subscription_table_two` ON subscription_table_one.subscription_id=relation_table.subscription_id
GROUP BY `customer_id`
Aaa, and one more thing, it's dynamic via PHP.
You can use the handy GROUP_CONCAT
function available with MySQL
. Below is the solution.
create table customer (customer_id int, customer_name varchar(100));
create table subscriptions (subscription_id int, subscription_name varchar(100));
create table customer_relation (subscription_id int, customer_id int);
insert into customer values (1,'cust1');
insert into customer values (2,'cust2');
insert into subscriptions values (1,'sub1');
insert into subscriptions values (2,'sub2');
insert into subscriptions values (3,'sub3');
insert into customer_relation values (1,1);
insert into customer_relation values (2,1);
insert into customer_relation values (3,1);
insert into customer_relation values (1,2);
insert into customer_relation values (3,2);
SELECT c.customer_id
, c.customer_name
, GROUP_CONCAT(s.subscription_name ORDER BY s.subscription_name) subs
FROM customer c
, subscriptions s
, customer_relation x
WHERE x.subscription_id = s.subscription_id
AND x.customer_id = c.customer_id
GROUP BY
c.customer_id
, c.customer_name;
Results:
customer_id customer_name subs
1 cust1 sub1,sub2,sub3
2 cust2 sub1,sub3
If you could use doctrine the this situation could be handled very easily. However doctrine itself returns object in such case alike as follows
Array
(
[0] => Array
(
[name] => customer_name
[id] => customer_id
[subscription] => Array
(
[0] => subscription_first
[1] => subscription_second
[2] => subscription_third
)
)
[1] => Array
(
[name] => customer_name
[id] => customer_id
[subscription] => Array
(
[0] => subscription_first
[1] => subscription_second
[2] => subscription_third
)
)
[2] => Array
(
[name] => customer_name
[id] => customer_id
[subscription] => Array
(
[0] => subscription_first
[1] => subscription_second
[2] => subscription_third
)
)
)
if you can make such an array then it will be very simple for you to make such presentation. Someone can write a complex query with multi level joins it will let a bigger bottle nick situation. But I suggest retrieve all data with a single join and parse using php engine to get such array and present the data.
Thanks
you can use union. Something like
SELECT * FROM CUSTOMER WHERE customer_id = ... UNION SELECT * FROM Subscriptions WHERE subscription_id IN (SELECT subscription_id WHERE customer_id = ...)
I don't know SQL at all, so there may be a lot of mistakes, but I think it does the job.
精彩评论