MySQL - GROUP_CONCAT() with joined table - unexpected results

join, mysql, sql

Solution

The problem is the `GROUP BY parentId`, place the `GROUP_CONCAT()` in a subquery and then use a `LEFT JOIN` on the subquery. When the `GROUP BY parentId` is equal to `null` those values are ignored so you return no result:

SELECT  a.`storeID`, 
        a.`typeID`, 
        a.`cost`, 
        a.`quantity` , 
        `reqItems` 
FROM    lpStore a
LEFT JOIN
(
  select  parentID,
     GROUP_CONCAT( CONCAT( quantity,  " x ", typeID ) ) AS  `reqItems` 
  from lpRequiredItems
  group by parentID
) c 
  ON ( a.storeID = c.parentID ) 
ORDER   BY  a.`storeID`, 
        a.`typeID`, 
        a.`cost`, 
        a.`quantity`
LIMIT 0 , 30

See SQL Fiddle with Demo

The result is:

| STOREID | TYPEID | COST | QUANTITY |                    REQITEMS |
--------------------------------------------------------------------
|       1 |   2514 | 2000 |        3 |          5 x 3668,10 x 4825 |
|       2 |   3199 | 1000 |        1 |                      (null) |
|       3 |   8785 | 5000 |        2 | 5 x 9813,1 x 4875,15 x 1102 |
|       4 |    579 | 1500 |        5 |                      (null) |

Problem

I have two tables - one that describes items that you can buy, and one that stores 'required items' to buy other items. There are some items in the first table that require other items as a form of trade. And sometimes you need a certain quantity of the required items. Here is the schema of the two tables: ``` +---------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------------+------+-----+---------+----------------+ | storeID | int(5) unsigned | NO | PRI | NULL | auto_increment | | itemID | int(10) | NO | | NULL | | | quantity | int(10) | NO | | NULL | | | cost | int(10) | NO | | NULL | | +---------------+-----------------+------+-----+---------+----------------+ +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | parentID | int(10) | NO | | NULL | | | itemID | int(10) | NO | | NULL | | | quantity | int(10) | NO | | NULL | | +----------+---------+------+-----+---------+-------+ ``` The second table links with the first one `storeID <-> parentID`. items in the store may have many required items. The problem: I need to run a query that returns all items in the store, but ALSO shows all the required items and their quantites. It should look something like this: ``` 4 x Item1,5 x Item2 ``` I have this current query: ``` SELECT a.*, b.typeName, GROUP_CONCAT(CONCAT(c.quantity, " x ", d.typeName)) as `reqItems` FROM lpStore a INNER JOIN typeNames b ON (a.typeID = b.typeID) INNER JOIN lpRequiredItems c ON (a.storeID = c.parentID) INNER JOIN typeNames ON (d.typeID = c.typeID) GROUP BY c.parentID ORDER BY a.cost ASC ``` `typeNames` is simply a table that links itemIDs with their actual names. Now, this query works well, however it seems to not include items that HAVE NO REQUIRED ITEMS. I suspect it's one of the JOINS, along with the GROUP BY (it might be grouping all the NULL values that don't exist in that table?), however I haven't been able to figure it out yet. I need it to return blank or NULL in that column if nothing is required. EDIT: Sample data I removed joining the tables with the item name table for simplicity Sample data available here: http://www.sqlfiddle.com/#!2/d8dca/1 Actual results: ``` +---------+--------+------+----------+-----------------------------+ | storeID | typeID | cost | quantity | reqItems | +---------+--------+------+----------+-----------------------------+ | 1 | 2514 | 2000 | 3 | 5 x 3668,10 x 4825 | | 3 | 8785 | 5000 | 2 | 1 x 4875,15 x 1102,5 x 9813 | | 4 | 579 | 1500 | 5 | NULL | +---------+--------+------+----------+-----------------------------+ ``` Expected results: ``` +---------+--------+------+----------+-----------------------------+ | storeID | typeID | cost | quantity | reqItems | +---------+--------+------+----------+-----------------------------+ | 1 | 2514 | 2000 | 3 | 5 x 3668,10 x 4825 | | 2 | 3199 | 1000 | 1 | NULL | | 3 | 8785 | 5000 | 2 | 1 x 4875,15 x 1102,5 x 9813 | | 4 | 579 | 1500 | 5 | NULL | +---------+--------+------+----------+-----------------------------+ ```

Original source