CODEWITHSUNDEEP
mysqljoinleft-joinprimary-keycomposite-primary-key
enano2054
enano2054

Reputation: 329

MySQL Query with LEFT JOIN where second table has a 2-Part Primary Key

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!

Upvotes: 0

Views: 113

Answers (1)

Shayllis Alves de Sousa
Shayllis Alves de Sousa

Reputation: 416

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.

Upvotes: 2

Related Questions