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

185
May 23, 2017, at 04:10 AM

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!

Answer 1

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.

READ ALSO
Get an integer and convert it to a date - mysql

Get an integer and convert it to a date - mysql

I am trying to extract the date from a members CPR numbers in my Database, its stored as an integer(CPR is Danish social security number and the first 6 characters are your date of birth)

289
calling a table in a SQL error

calling a table in a SQL error

hello I was working on a sql Query to get the select count, but it says error, when prepraing the consulte, would you mind to help me guys, with this problemhere is the structure of my tables

164
What is the best practice for designing an SQL table that relates to table A, table B, or table A and B?

What is the best practice for designing an SQL table that relates to table A, table B, or table A and B?

Let's say I have three tablesI'm going to make these tables as simple as possible to illustrate the design question:

134
Delete Users data from the database based upon ID

Delete Users data from the database based upon ID

I have 100 tables in my MYSQL and I use the following query to find all the tables which has the specific Column:

98