MySQL Self Join Table

Question

I have a table named Inventory with the following structure:

Location_ID |Item_ID |Stock
1           |A       |100
1           |B       |500
1           |C       |300
2           |A       |10
2           |B       |20

field location_ID and item_ID are composite key. I want to produce the following data from that single table:

Item_ID |Stock_1 |Stock_2
A       |100     |10
B       |500     |20
C       |300     |0

I tried writing several self join queries but it doesn't work. There is also another problem: Item_ID C does not exist on location_ID 2. How can we put the value '0' on the resulting table if it does not exist? Can someone with brighter mind shed any light?

possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Zohar Peled, Apr 30 '15 at 06:10
I am reading the tutorial about pivot table and still have not figure out how to apply it to my problem yet — Albert Tobing, Apr 30 '15 at 06:31

Jackie Dong · Accepted Answer · 2015-04-30T06:44:12.150

3

select DIS_ITME_ID,
       IFNULL ((select stock
                 from inventory
                where location_id = 1
                  and item_id = DIS_ITEM_ID), 0) as stock_1,
       IFNULL ((select stock
                 from inventory
                where location_id = 2
                  and item_id = DIS_ITEM_ID), 0) as stock_2
  from (select distinct item_ID as DIS_ITEM_ID from inventory)

edited Apr 30 '15 at 06:44

answered Apr 30 '15 at 06:17

Jackie Dong

813
1
5
20

I'm not a mysql guy, but it probably should be `isnull` and not `ifnull` – Zohar Peled Apr 30 '15 at 06:40
IFNULL syntax is correct. I am reading tutorial on your pivot table link. Hopefully i can figure something out. thx for your suggestion – Albert Tobing Apr 30 '15 at 06:44
Finally got it working. Fixed the syntax error from your code. It should be: – Albert Tobing Apr 30 '15 at 06:58
SELECT DIS_ITEM_ID, IFNULL ( (SELECT stock FROM inventory WHERE location_ID='1' AND item_id=DIS_ITEM_ID), 0) AS stock_1, IFNULL ( (SELECT stock FROM inventory WHERE location_ID = '2' AND item_id =DIS_ITEM_ID), 0) AS stock_2 FROM (SELECT DISTINCT item_ID AS DIS_ITEM_ID FROM inventory ORDER BY item_ID) AS t – Albert Tobing Apr 30 '15 at 06:58
Please edit your code so I can mark it as the correct answer :) – Albert Tobing Apr 30 '15 at 06:58
you need to add the [ AS t ] at the end of your code otherwise it will throw "every derived table must have its own alias" error. – Albert Tobing Apr 30 '15 at 07:05

score 1 · Answer 2 · answered Apr 30 '15 at 07:22

I know it's probably too late but there is a simpler way:

SELECT Item_Id,
       SUM(
       CASE WHEN Location_ID = 1 THEN
         Stock
       ELSE 
          0 
       END) As Stock1,
       SUM(
       CASE WHEN Location_ID = 2 THEN
         Stock
       ELSE 
          0 
       END) As Stock2
FROM Inventory
GROUP BY Item_Id

sqlfiddle

score 0 · Answer 3 · answered Apr 30 '15 at 06:39

0

You can use a dinamic pivot query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(i.location_ID = ''',
      CAST(location_ID as CHAR(50)),
      ''', i.stock, 0)) AS stock_',
      CAST(location_ID as CHAR(50))
    )
  ) INTO @sql
FROM inventory;
SET @sql = CONCAT('SELECT i.item_ID, ', @sql, ' 
                   FROM inventory i
                   GROUP BY i.item_ID');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

You can find more info here: http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

answered Apr 30 '15 at 06:39

Gabor Rajczi

471
2
9

I dont think i can use that solution as I will execute the query using ADO.NET. I need pure SQL Query solution. Thx for the input though. – Albert Tobing Apr 30 '15 at 06:43
@AlbertTobing: I think You can. First, collect the available stocks (ie. location_IDs), and make the runable SQL statement as string. Second, run the query. But You are right, You can not copy and paste this solution. – Gabor Rajczi Apr 30 '15 at 06:58
If You have just two location, then the solution of @djonga3 is right for You. But if the are variable number of stocks, the You must generate it dinamically. – Gabor Rajczi Apr 30 '15 at 07:29

MySQL Self Join Table

3 Answers3