Mysql joins using joining table

[eluser]ppwalks[/eluser]
Hi all I would like some help with a query for my admin panel for shop backend, I am trying to display all products on the page and next them I would like to display what category they belong to in a table. I figure I will need two joins to match the data but am struggling to get the correct joins, so someone please help.

I am new to MYSql and trying to learn it.

Here is my table structure...

Products table (product)

->product_id
->product_name
->product_description and so on for all the rest....

Joining table (product_categories)

->product_id
->category_id

Category Table (category) and what

->category_id
->category_name

I would like to select all the products from "product table" and what categories that product belongs to..

thanks for any help

Paul

[eluser]theprodigy[/eluser]

I would like to select all the products from “product table” and what categories that product belongs to.

select *
from product
join product_categories on product.product_id = product_categories.product_id
join category on category.category_id = product_categories.category_id

[eluser]vitoco[/eluser]
This will get you what you want

Code:

SELECT
  p.product_id ,
  p.product_name ,
  p.product_description ,
  COUNT(  DISTINCT( pc.category_id ) ) AS num_distinct_categories_product ,
  COUNT(  pc.category_id ) AS num_assigned_categories_product ,
  GROUP_CONCAT( DISTINCT( pc.category_id ) ) AS category_ids ,
  GROUP_CONCAT( DISTINCT( c.category_name ) ) AS category_names
FROM
  product AS p
LEFT JOIN
  product_categories AS pc
USING
  ( product_id )
LEFT JOIN
  category AS c
USING
  ( category_id )
GROUP BY
  product_id

Note that the type of join is "LEFT" because, will get you all products even if has 0 categories, also, if the PK in product_categories is not product_id/category_id , num_distinct_categories_product AND num_assigned_categories_product could be different.

Slds.

[eluser]ppwalks[/eluser]
Thank you ever so much guys that sorted it...