CODEWITHSUNDEEP
mysqlsqljoin
blitzmann
blitzmann

Reputation: 7905

MySQL - GROUP_CONCAT() with joined table - unexpected results

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

Upvotes: 4

Views: 2069

Answers (3)

Taryn
Taryn

Reputation: 247680

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

Upvotes: 4

John Woo
John Woo

Reputation: 263723

You just need to use LEFT JOIN instead of INNER 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

Upvotes: 0

user1965813
user1965813

Reputation: 671

The problem is the "Inner join", which only returns values that have a match, i.e. also an 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

Upvotes: 0

Related Questions