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                        |
+---------+--------+------+----------+-----------------------------+

      

+3


source to share


3 answers


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

      

See SQL Fiddle with Demo



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) |

      

+3


source


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 

      

0


source


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

      

0


source







All Articles