Complex SQL query :: display data inline from multiply tables

Question

Hi i have a proble in finding solution.

I have 3 tables:

1. Customer
   • customer_id
   • customer_name
2. Relation
   • subscription_id
   • customer_id
3. 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_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.

Answer 1

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.

Answer 2

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

Answer 3

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