CODEWITHSUNDEEP
mysql
user1336827
user1336827

Reputation: 1826

Concat child items in to column by id

I have 2 tables. the first is the item table with columns:

itemId|itemNumber|description
1      abc        xxxx
2      def        xxxx
3      hij        xxxx

Then I have a subitem table with columns:

parentItemId|childItemId
1            2
2            3 
1            3
3            2
3            1

Im trying to select a list of itemNumbers with the list of child itemNumbers concatenated in the 3rd column like so: results:

ItemNumber|description|childItemNumber1, childItemNumber2, childItemNumber3...
abc        xxxx        def,hij
def        xxxx        hij
hij        xxxx        def,abc

So i need to look up the item numbers for the parent child relationship in the first table before concatenating them.

ive tried something like this:

SELECT c.itemNumber, c.description,  GROUPBY_CONCAT(SELECT a.itemNumber from Item as a inner join SubItem as b on a.itemId = b.childItemId where a.Itemid = b.parentItemId) FROM Item as c WHERE 1

but i dont even think its close

Upvotes: 1

Views: 161

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

This requires two joins and an aggregation:

select p.itemNumber, p.description,
       GROUP_CONCAT(ci.description) as children
from Item p left join
     SubItem s
     on p.itemId = s.parentItemId left join
     Item ci
     ON s.childItemId = ci.ItemId
group by p.ItemId;

Subqueries are not necessary.

Upvotes: 1

Related Questions