Getting extra data from the join table

Question

Ok so i have this structure

PRODUCTS

* PRODUCT_ID (primary key)
* PRODUCT_NAME

CATEGORIES

* CATEGORY_ID (primary key)
* CATEGORY_NAME
* CODE

PRODUCT_CATEGORIES_MAP

* PRODUCT_ID (primary key, foreign key to PRODUCTS)
* CATEGORY_ID (primary key, foreign key to CATEGORIES)
* QUANTITY

I am using this query

SELECT * FROM `products`
where id in (
    SELECT `product_id`
    FROM `categorizations`
    WHERE category_id = (
        SELECT id
        FROM `categories`
        where code = 'something'))

this works great but i am just getting a list of products. I need the quantity of each one one in the join table

Answer 1

sql's a bit rusty but here goes

select PRODUCT_ID, PRODUCT_NAME, QUANTITY
from PRODUCTS as A, PRODUCTS_CATEGORIES_MAP as B, CATEGORIES as C
where A.PRODUCT_ID = B.PRODUCT_ID
and C.CATEGORY_ID = B.CATEGORY_ID
and C.CODE = 'something'

Answer 2

The table names in your explanation are different from your sample query. I used the ones from your query.

SELECT p.product_id  ,
       p.product_name,
       pc.quantity
FROM   `products` p
       JOIN `categorizations` pc
       ON     p.id = pc.`product_id`
       JOIN `categories` c
       ON     c.id = pc.category_id
WHERE  c.code      = 'something'

Answer 3

The detail that you want is the quantity of the product and so, why not start by them....you can always retrieve the products because you have the product_id.

SELECT * FROM (SELECT `product_id`, `quantity`
    FROM `categorizations`
    WHERE category_id = (
        SELECT id
        FROM `categories`
        where code = 'something'))) AS `filtered_categorization`
LEFT JOIN `products`
    ON (`products`.`id` == `filtered_categorization`.`product`);

Answer 4

You may want to sum over quantity in case product is in more categories.

select p.*, 
       sum(m.quantity) quantity
  from products p
  join categorizations m on m.product_id = p.product_id
  join categories on c.category_id = m.category_id 
   and c.code = 'something'
 group by p.product_id

Otherwise look at @Martin code

Answer 5

try this...

select
 p.*,
 c.category_id,
 c.category_name,
 pcm.quantity
from
 products p
inner join product_categories_map pcm on p.product_id = pcm.product_id
inner join categories c on pcm.category_id = c.category_id
where
 c.code = 'something'
order by
 p.product_id, c.category_id;