CODEWITHSUNDEEP
mysqljoin
mohsin
mohsin

Reputation: 604

mysql joins with many to many relationship

I am trying to learn joins with many to many relationships in mysql, I have four tables:

customers, orders, products, payments

I am trying to get records as: 

customer_name, order_status, pay_method, pro_name

the query I use:

SELECT cust_name,order_status,pay_method,pro_name FROM customer
INNER JOIN orders ON customer.cust_id = orders.cust_id
INNER JOIN payments ON payments.order_id = orders.order_id
INNER JOIN products ON products.pro_id = orders.pro_id

I am receiving results as I want with no issue. But this query shows only one product against one order, then I realize I should have another separate table which will hold many products against one order. In this issue I am not able to get desired result

Upvotes: 1

Views: 63

Answers (1)

MHardwick
MHardwick

Reputation: 644

It's not entirely clear what you're asking, but I'm guessing what you're trying to do is create a many to many table that links orders and products? In which case, you can just create a table called "productorders" which will contain an order_id and a pro_id. Then you would modify your query like this:

SELECT cust_name,order_status,pay_method,pro_name FROM customer
INNER JOIN orders ON customer.cust_id = orders.cust_id
INNER JOIN payments ON payments.order_id = orders.order_id
INNER JOIN productorders ON productorders.order_id = orders.order_id
INNER JOIN products ON products.pro_id = productorders.pro_id;

Joining productorders will get all products associated with an order, and then joining products will get the information associated with each product.

Upvotes: 1

Related Questions