MySQL - GROUP_CONCAT () with concatenated table - unexpected results
I have two tables - one that describes the items you can buy and one that stores the "necessary items" in order to buy other items. There are some elements in the first table that require different positions as a form of trading. And sometimes you need a certain amount of required items. Here's a diagram 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 is linked to the first storeID <-> parentID
. items in a store can have many required items.
Problem: I need to run a query that returns all items in the store, but ALSO shows all required items and their quanta. It should look something like this:
4 x Item1,5 x Item2
I have this current request:
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 just a table that associates ItemIDs with their actual names.
Now this query is working well, however, it doesn't seem to include items that are NOT REQUIRED ITEMS . I suspect this is one of the JOINS along with GROUP BY (could it be a grouping of all NULL values that are not in this table?), However I have not been able to figure it out yet. I need it to return empty or NULL on that column if nothing is required.
EDIT: Sample data
I removed the join of tables with table of item names for simplicity
Sample data is 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 |
+---------+--------+------+----------+-----------------------------+
source to share
The problem is GROUP BY parentId
, put GROUP_CONCAT()
in a subquery and then use LEFT JOIN
in a subquery. When the value GROUP BY parentId
is null
, those values are ignored, so you don't return a 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
Result:
| 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) |
source to share
The problem is with the "Inner Join", which only returns the values that have a match, that is, also the entry in the "required" table. Try a left join instead.
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)
LEFT 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
source to share
You just need to use LEFT JOIN
insteadINNER JOIN
SELECT a.`storeID`,
a.`typeID`,
a.`cost`,
a.`quantity` ,
GROUP_CONCAT( CONCAT( c.quantity, " x ", c.typeID ) ) AS `reqItems`
FROM lpStore a
LEFT JOIN lpRequiredItems c
ON ( a.storeID = c.parentID )
GROUP BY c.parentID
ORDER BY a.`storeID`,
a.`typeID`,
a.`cost`,
a.`quantity`
LIMIT 0 , 30
source to share