0

I have 2 tables in a MySQL database (storeskus). The first is FBM_Orders and the second is IM_INV.

I am trying the query 

SELECT `FBM_Orders`.`order-id`,`FBM_Orders`.`order-item-id`,`FBM_Orders`.`purchase-date`,
       `FBM_Orders`.`promise-date`,`FBM_Orders`.`buyer-name`,`FBM_Orders`.`sku`,
       `FBM_Orders`.`product-name`,`FBM_Orders`.`quantity-purchased`,
       `FBM_Orders`.`recipient-name`,`IM_INV`.`LOC_ID`,`IM_INV`.`QTY_ON_HND`
FROM `FBM_Orders` 
LEFT JOIN `IM_INV` ON `FBM_Orders`.`sku` = `IM_INV`.`ITEM_NO`
WHERE `FBM_Orders`.`quantity-to-ship` > 0
ORDER BY `FBM_Orders`.`purchase-date`, `IM_INV`.`LOC_ID` ASC;

Because the IM_INV table has a 2-part primary key: ITEM_NO & LOC_ID, I am getting 4 lines for each ITEM_NO with the QTY_ON_HND for each of the 4 locations (LOC_ID).

I am fairly new to SQL so I'm thrilled to have gotten this far, but how can I make it so that the result is a single line per ITEM_NO but with a column for each LOC_ID with its QTY_ON_HND?

Example:

My current result is

FBM_Order.sku    FBM_Order.quantity-purchased    IM_INV.LOC_ID    QTY_ON_HND
'SCHO645256'     1                               AF               2
'SCHO645256'     1                               LO               2
'SCHO645256'     1                               S                3
'SCHO645256'     1                               SL               1

How can I change that to

FBM_Order.sku    FBM_Order.quantity-purchased   QTY_ON_HND_AF    QTY_ON_HND_LO    QTY_ON_HND_S    QTY_ON_HND_SL
'SCHO645256'     1                              2                2               3               1

?

Thanks!

enano2054
  • 329
  • 6
  • 19
  • Do you mean that the second table has a 2-part primary key? – Tim Grant May 22 '17 at 20:23
  • @TimGrant Probably. My apologies. Still learning the proper lingo. I'll update my post. Thanks for the correction. – enano2054 May 22 '17 at 20:24
  • It looks like you want to `pivot` the data on the SKU, using LOC_ID as the column names and using QTY_ON_HND as values. https://stackoverflow.com/questions/7674786/mysql-pivot-table is an example: you can use case statments wrapped in max() aggregates on the QTY ON_HAND to achieve the desired results. grouping by SKU order to achieve this. However it does assume you know the possible values for LOC_ID; as each would require a seperate case syatement. if LOC_ID could be hundreds; but limited to 4-5 for each sku, then you would have to use Dynamic SQL to accomplish this. – xQbert May 22 '17 at 20:37
  • 1
    https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns provides a DYNAMIC SQL solution. Either solution works; but we woudl need better understanding of LOC_ID and if you will always be filtering down to just one SKU when using this query. on a editorial note: Data formatting and presentation really belongs in the UI layer not the data exaction layer. – xQbert May 22 '17 at 20:38
  • @xQbert So I know that LOC_ID is only going to have 4 values for a good while; each LOC_ID represents a brick-and-mortar store that I have so I'm not opening new stores all the time nor closing them. But what I'm understanding from you, in that case, is there isn't a good way to future proof the query without DYNAMIC SQL correct? That is good information. I can probably work with the result I am getting in that case. – enano2054 May 22 '17 at 20:41
  • Yes, w/o Dynamic SQL it can't be future proofed. w/ 4 case statements in the 1st linked comment shows how this could be done when only 4 locations exist. – xQbert May 22 '17 at 20:41
  • @xQbert cool I appreciate it. I can have the application work with the current result but I thought I would see if there was a way to do it in SQL given that I am still learning SQL. Thanks again – enano2054 May 22 '17 at 20:42
  • 1
    Yes there is a way and yes it is often done; and it works well. But should it be done there for ease of growth and scaling... that's a different question to which you'll get a variety of answers. I'd say it depends on lots of factors. A purist woudl say the data layer and presentation layer should always be kept seperate. and since this is a presentation of the data question, the two should be kept seperate. However, I'm of the belief that you should return the data the way you need it and only what you need. So if I can do it in the database I often will; but not always. – xQbert May 22 '17 at 20:49

1 Answers1

2

You may load it as you already do and treat it inside your application, but if you really wanna make that inside your MySQL, try GROUP CONCAT and JSON as follows:

SELECT
  GROUP_CONCAT(JSON_OBJECT(
    'LOC_ID', IM_INV.LOC_ID,
    'QTY_ON_HND', QTY_ON_HND
  ))
  {another fields}
FROM `FBM_Orders` 
LEFT JOIN `IM_INV` ON `FBM_Orders`.`sku` = `IM_INV`.`ITEM_NO`
WHERE `FBM_Orders`.`quantity-to-ship` > 0
GROUP BY `FBM_Orders`.`order-id`;

Note: JSON is just available for MySQL 5.7+ and may slow down your query a little bit. You're still gonna need convert your data to array inside your application. So it's half done inside your app and half inside your database.

Shayllis Alves de Sousa
  • 431
  • 3
  • 11
  • This can be done in mySQL... SHOULD it be done there is a good question. but dynamic pivots and pivot examples linked in comments above show that it CAN be done. – xQbert May 22 '17 at 20:40
  • @xQbert Say I was using DYNAMIC SQL, would the "should" answer turn to "yes"? Just for my own edification. – enano2054 May 22 '17 at 20:44
  • Pivot is not supported in MySQL by default, you can simulate it part inside you database, part inside your app once you need to convert json data into array inside your app.Sou you've give me a negative point for a right answer. – Shayllis Alves de Sousa May 22 '17 at 20:45
  • @ShayllisAlvesdeSousa the negative point was for the comment, "You can't do this inside mySQL" that is not true as illustrated by 2 links and several other examples. PIVOT isn't supported.. but it can be done in SQL. Just like FULL outer join isn't supported but it still can be done with two outer joins and a union. – xQbert May 22 '17 at 20:47
  • This falls on the side of presenting data in the application. Which is fine. Either approach works. and both work and seem to scale but on a Enterprise level application for resale, I'd put the presentation logic in the presentation layer. I wouldn't use the dynamic SQL. For a small to mid size business I'd put it in the database; because I don't have to think as hard about the UI design at a cost of enterprise scaling (provided the small/midsize have no intention of massive scaling) – xQbert May 22 '17 at 20:53