CODEWITHSUNDEEP
mysqlsqlrelational-database
padarom
padarom

Reputation: 3648

MySQL multiple joins with Many-To-Many relations

I have three tables that matter to me: customer, client_assignment and customer_products. The last two are assignment tables for Many-To-Many-relations.

In client_assignment a customer of the type client gets associated with another customer (where customer_id is the parent and client_id the child).
In customer_product I associate customers with products.

A client can not be associated with a product, he inherits it from his parent-customer. In the example below this means, that Foo-1 also has product 3, because his father (Foo) has it.

customer (Customers):
+-------------+-------+----------+
| customer_id | name  | type     |
+-------------+-------+----------+
|           1 | Foo   | customer |
|           2 | Foo-1 | client   |
|           3 | Foo-2 | client   |
|           4 | Bar   | customer |
|           5 | Foob  | customer |
+-------------+-------+----------+

client_assignment (Customer/Client-Assignment):
+-------------+-----------+
| customer_id | client_id |
+-------------+-----------+
|           1 |         2 |
|           1 |         3 |
+-------------+-----------+

customer_product (Customer/Product-Assignment):
+-------------+------------+
| customer_id | product_id |
+-------------+------------+
|           1 |          3 |
|           1 |          4 |
|           1 |          5 |
|           4 |          3 |
|           5 |          7 |
+-------------+------------+

I want to accomplish the following: Select all customers and their respective clients that are associated with product X.

My desired result for product 3 is something like this:

+-------------+-------+--------+
| customer_id | name  | parent |
+-------------+-------+--------+
|           1 | Foo   | null   |
|           2 | Foo-1 | 1      |
|           3 | Foo-2 | 1      |
|           4 | Bar   | null   |
+-------------+-------+--------+

I've been thinking about this for a bit and it seems fairly complicated. I've tried joining them like the following:

SELECT c2.customer_id, c2.name, c1.customer_id as parent
  FROM  customer_product p, customer c1, customer c2, client_assignment a
 WHERE 
           c1.customer_id = p.customer_id 
       AND c2.customer_id = a.client_id 
       AND a.customer_id = c1.customer_id 
       AND p.product_id = 3

I know, that this query will not give me the exactly desired result, but I've created it to start with. The main problem about it is, that it does only select the clients, and not the customers themselves. Therefore I only get Foo-1 and Foo-2 as a result, but not Bar or Foo.

The question is: Is this reasonably easily achievable, and how?

Upvotes: 0

Views: 49

Answers (2)

Barmar
Barmar

Reputation: 780818

You can write another SELECT that gets the customers themselves, and combine the two with UNION:

SELECT c.customer_id, c.name, NULL AS parent
  FROM customer AS c
  JOIN customer_product AS p ON c.customer_id = p.customer_id
 WHERE c.type = 'customer' 
  AND p.product_id = 3

UNION

SELECT c2.customer_id, c2.name, c1.customer_id AS parent
 FROM customer_product AS p
 JOIN customer AS c1 ON c1.customer_id = p.customer_id
 JOIN client_assignment AS a ON a.customer_id = c1.customer_id
 JOIN customer AS c2 ON c2.customer_id = a.client_id
WHERE c2.type = 'client'
  AND p.product_id = 3

Upvotes: 2

saikumarm
saikumarm

Reputation: 1575

Because you have to get names of both clients and parents of the client, a way of doing it is by using UNION.

with customer_names as (select customer_id from s_customer_product where product_id =3),

client_names as (select client_id , customer_id as Parent_id from s_client_assignment join customer_names using(customer_id))

select customer_id , name , null as Parent from  s_customers join customer_names using(customer_id) 

union 

select a.client_id , b.name , a.parent_id as Parent from client_names a, s_customers b where b.customer_id = a.client_id;

Upvotes: 0

Related Questions