CODEWITHSUNDEEP
mysql
Dominik Knapo
Dominik Knapo

Reputation: 1

Multiple values returned from different table

I have a question regarding query I'm working on right now:

I have these 5 tables in database: Items, ModelItems, Category, Brand, Codes

Items:

ModelItems:

Category:

Brand:

Codes:

I am selecting each product from Items table, which has a model in ModelItems and trough that it gets assigned Category.Name and Brand.Name, all of this I can handle, my problem is with Codes table. Where in Codes there are multiple values of EAN and PN assigned to one Items_id and I don't really know how to access it.

For now I've come up with this:

SELECT Items.*, Category.name as category, Brand.name as brand, 
FROM Items 
JOIN ModelItems ON ModelItems.id = Items.ModelItems_id 
JOIN Category ON Category.id = ModelItems.Category_id 
JOIN Brand ON Brand.id = ModelItems.Brand_id 
WHERE Brand.name = ?

Which does not handle the code problem. I've tried to search for something like this, but wasn't able to find anything. I would like output from this query to be something like this:

id, Name, ModelItems_id, category, brand, [EAN, EAN, EAN], [PN, PN, PN]

I hope my explanation is clear enough :-)

Upvotes: 0

Views: 71

Answers (2)

Manchikanti Aishwarya
Manchikanti Aishwarya

Reputation: 290

SELECT 
  it.id,
  it.`name`,
  md.`id`,
  cat.`name`,
  br.`name`,
  GROUP_CONCAT(co.`ean`),
  GROUP_CONCAT(co.`pn`) 
FROM
  items it 
  INNER JOIN modelitems md 
    ON md.`id` = it.`modelitems_id` 
  INNER JOIN category cat 
    ON cat.`id` = md.`category_id` 
  INNER JOIN brand br 
    ON br.`id` = md.`brand_id` 
  INNER JOIN codes co 
    ON it.`id` = co.`items_id` 
    WHERE br.`name` = '?'
GROUP BY it.`id`

Upvotes: 2

Boris Schegolev
Boris Schegolev

Reputation: 3701

How about that?

SELECT Items.*, Category.name AS category, Brand.name AS brand, 
    (SELECT group_concat(EAN SEPARATOR ', ') FROM Codes WHERE Codes.Items_id = Items.id GROUP BY Codes.Items_id) AS ean_list,
    (SELECT group_concat(PN SEPARATOR ', ') FROM Codes WHERE Codes.Items_id = Items.id GROUP BY Codes.Items_id) AS pn_list
FROM Items 
JOIN ModelItems ON ModelItems.id = Items.ModelItems_id 
JOIN Category ON Category.id = ModelItems.Category_id 
JOIN Brand ON Brand.id = ModelItems.Brand_id 
WHERE Brand.name = ?

Upvotes: 0

Related Questions