使用JOIN匹配第一个结果

Question

I'm trying to obtain the price from a field called DiscountMarkupPriceRate1 on my second table to display in PHP.

The first portion of my query pulls what I need correctly, the Parentsku of all visible products with inventory. ie GTR101.

I'm trying to join it with a second table and retrieve only the first DiscountMarkupPriceRate1 for the parent (GTR101%) where ItemCustomerPriceLevel is M.

Here's what my table looks like. This is essentially the result of the first half of my query before the join (stripped of all the other fields I need):

**INVENTORY**
SKU      store_quantity     parent_sku  visible
----------------------------------------------------------------
GTR101      20      NULL        Y
GTR102      100     NULL        Y
GTR103      88      NULL        Y

This is the second table:

**ins_imb_1**

DiscountMarkupPriceRate1    ItemNumber   ItemCustomerPriceLevel           
-----------------------------------------------------------------
15.950             GTR101S          M
15.950             GTR101M          M
11.950             GTR101L          M
10.000             GTR101S          T

I'm trying to get

GTR101   15.95

and here's what I have for a query:

Select * 
from INVENTORY
where parent_sku='' 
AND store_quantity > 0 
AND SKU like '%GTR%' 
AND visible='Y'

LEFT JOIN ins_imb_1
ON ins_imb_1.ItemNumber =
    (
    SELECT ItemNumber, ItemCustomerPriceLevel, DiscountMarkupPriceRate1
    FROM ins_imb_1
    WHERE ins_imb_1.ItemNumber% = INVENTORY.SKU 
    AND ins_imb_1.ItemCustomerPriceLevel = 'M'
    ORDER BY 
            INVENTORY.SKU
    LIMIT 1
    )

Answer 1

First thing wrong I see here, is that JOINs need to be after the FROM statement and before WHERE.

Also your subquery in the LEFT JOIN is wrong.

LEFT JOIN ins_imb_1
ON ins_imb_1.ItemNumber =
(
  SELECT ItemNumber, ItemCustomerPriceLevel, DiscountMarkupPriceRate1

Your subquery should only return one field (for it to compare to ins_imb_1.ItemNumber).

I don't know if you even need a subquery here, you could do something like this:

LEFT JOIN ins_imb_1
ON ins_imb_1.ItemNumber LIKE CONCAT(INVENTORY.SKU, '%')
AND ins_imb_1.ItemCustomerPriceLevel = 'M'

I also see few things that could be optimized here.

where parent_sku='' should be where parent_sku IS NULL.

AND SKU like '%GTR%' should be AND SKU like 'GTR%'(as the SKU always start with 'GTR').

SELECT *
FROM INVENTORY

LEFT JOIN ins_imb_1
ON ins_imb_1.ItemNumber LIKE CONCAT(INVENTORY.SKU, '%')
AND ins_imb_1.ItemCustomerPriceLevel = 'M'

WHERE parent_sku IS NULL
AND store_quantity > 0
AND SKU LIKE 'GTR%'
AND visible = 'Y'