MySQL join many to many single row

join;MySQLsql

I have 3 tables that I want to combine, see below for details:

product

productID
name
price

prod_cat

productID
categoryID

joined

product.productID category.categoryID product.name product.price category.name(each one though, since a product can belong to more than one category)

What I want to do is get each product with the categories that relate to them in a single query. How would I got about this?

Best Answer

You need two joins:

SELECT
    product.productID,
    category.categoryID,
    product.name,
    product.price,
    category.name
FROM product
JOIN product_cat ON product.productID = product_cat.productID
JOIN category ON category.categoryID = product_cat.categoryID

If a product could be in no categories and you still want to return it, change JOIN to LEFT JOIN in both places.

An alternative approach:

SELECT
    product.productID,
    product.name,
    product.price,
    GROUP_CONCAT(category.name)
FROM product
JOIN product_cat ON product.productID = product_cat.productID
JOIN category ON category.categoryID = product_cat.categoryID
GROUP BY product.productID

However it might be better just to use two queries instead of putting multiple values into a single cell.

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.