Yanick Lafontaine Yanick Lafontaine - 1 month ago 8
MySQL Question

MySQL Left Join - Not empty or first row

We have two table,

PRODUCT_INDEX :

+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| PRODUCT_NUMBER | varchar(25) | NO | PRI | | |
| VENDOR_NUMBER | varchar(10) | NO | PRI | | |
| VENDOR_PRODUCT | varchar(25) | YES | MUL | | |
+----------------+---------------+------+-----+---------+-------+


PRODUCT :

+------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| PRODUCT_NUMBER | varchar(25) | NO | PRI | | |
| DESCRIPTION_ENG | varchar(500) | YES | MUL | | |
| STATUS | varchar(1) | YES | | | |
+------------------+---------------+------+-----+---------+-------+


if we run this query one PRODUCT_INDEX :

SELECT * FROM PRODUCT_INDEX WHERE PRODUCT_NUMBER = '900338'


We get 3 result in this order.

+----------------+---------------+----------------+
| PRODUCT_NUMBER | VENDOR_NUMBER | VENDOR_PRODUCT |
+----------------+---------------+----------------+
| 900338 | F00045 | |
| 900338 | F00509 | |
| 900338 | F01041 | CSR6719-2 |
+----------------+---------------+----------------+


Now i need to do a query selecting all product from table PRODUCT and JOIN only one row from table PRODUCT_INDEX. If field VENDOR_PRODUCT is not empty for one line we would like to take this line first. If all line contain field VENDOR_PRODUCT we would like to take first row and if all line doesn't contain field VENDOR_PRODUCT we would like to take first row to.

For now i have traditional left join query (for this example i select only one product but my query is for all product in PRODUCT table):

SELECT
P.PRODUCT_NUMBER,
P.DESCRIPTION_ENG,
P.STATUS,
PCI.VENDOR_NUMBER,
PCI.VENDOR_PRODUCT
FROM
PRODUCT P
LEFT JOIN
PROD_CROSS_INDEX PCI
ON
PCI.PRODUCT_NUMBER = P.PRODUCT_NUMBER
WHERE
P.PRODUCT_NUMBER = '900338';


This query return 3 row because they have 3 row matching in PRODUCT_INDEX.

How can i do my query?

EDIT 2 :

Here is my complete query right now :

SELECT
P.*,
IP.master AS MASTER_PACK,
IP.inner AS INNER_PACK,
PO.INFO1,
PO.INFO2,
PO.INFO3,
PO.INFO4,
PO.INFO5,
PO.INFO6,
PO.INFO7,
PO.INFO8,
PO.INFO9,
PO.INFO10,
PO.INFO11,
PO.INFO12,
PO.MEMO_ENG1,
PO.MEMO_FR1,
PO.FLAGS1,
PO.MEMO_ENG2,
PO.MEMO_FR2,
PO.FLAGS2,
PO.MEMO_ENG3,
PO.MEMO_FR3,
PO.FLAGS3,
PO.PICTURE,
PP.PRICE_1,
PP.PRICE_2,
PP.PRICE_3,
PP.PRICE_4,
PP.PRICE_5,
PP.PRICE_6,
MAX(IFNULL(PCI.VENDOR_PRODUCT, 0)) AS VENDOR_PRODUCT
FROM
PRODUCT P
LEFT JOIN
PRODUCT_OTHER PO
ON
PO.PRODUCT_NUMBER = P.PRODUCT_NUMBER
LEFT JOIN
PRODUCT_PRICES PP
ON
PP.PRODUCT_NUMBER = P.PRODUCT_NUMBER
LEFT JOIN
addison_intranet.ae_produit IP
ON
IP.sku = P.PRODUCT_NUMBER
LEFT JOIN
PROD_CROSS_INDEX PCI
ON
PCI.PRODUCT_NUMBER = P.PRODUCT_NUMBER
WHERE
P.STATUS != 'D' AND P.MASTER = '' AND P.PRODUCT_NUMBER NOT LIKE '%ECH%' AND P.PRODUCT_NUMBER NOT LIKE '%CARTE%' AND P.PRODUCT_NUMBER NOT LIKE '%ZZZ%' AND P.UNIT != 'KIT'
GROUP BY
P.PRODUCT_NUMBER
ORDER BY
P.PRODUCT_NUMBER

Answer

You can use an aggregation function

  SELECT
    P.PRODUCT_NUMBER,
    P.DESCRIPTION_ENG,
    P.STATUS,
    max(ifnull(PCI.VENDOR_NUMBER, 0)),
    max(ifnull(PCI.VENDOR_PRODUCT, 0)
  FROM
    PRODUCT P
  LEFT JOIN
    PROD_CROSS_INDEX PCI
  ON
    PCI.PRODUCT_NUMBER = P.PRODUCT_NUMBER
  GROUP BY   P.PRODUCT_NUMBER,    P.DESCRIPTION_ENG, P.STATUS
  ORDER BY PCI.VENDOR_PRODUCT desc limit 1;