Joining four mySQL tables without a single common value

0
votes

I have four tables that I need to pull data from. Three of the tables have a common value (product_id) the fourth table has a value in common with the third table (category_id). So they look something like this:

Products: product_id, name, quantity, image, weight

Product_Description: product_id, Description

Product_to_Category: product_id, category_id

Category: category_id, category_name

I need to pull all of the data and combine it into a result that looks like this Name, Quantity, Image, Weight, Description, Category name

I know how to do a JOIN that works for the first three tables but I don't know how to add the into the results.

1
mysql
Start by writing as much of the join as you can in your question. – Eric J.

1 Answers

2
votes

Just add an additional join on the category_id:

select p.name, p.quantity, p.image, p.weight, pd.description, c.category_name
from products p 
    join product_description pd on p.product_id = pd.product_id
    join Product_to_Category pc on p.product_id = pc.product_id
    join category c on pc.category_id = c.category_id