JOIN two tables and GROUP by the common column

Question

I'm new to MYSQL and struggling to form a query that joins two tables and returns a unique product row.

TABLE product:

╔══════════════════════════════╦══════════╦════════════╦════════════════════╗
║             ref              ║  brand   ║    mpn     ║        sku         ║
╠══════════════════════════════╬══════════╬════════════╬════════════════════╣
║ 0001___DOGICLON___912-101242 ║ DOGICLON ║ 912-101242 ║ 000000000001082649 ║
║ 0002___DOGICLON___912-101242 ║ DOGICLON ║ 912-101242 ║ 912-101242         ║
║ 0003___Dogiclon___912-101242 ║ Dogiclon ║ 912-101242 ║ 912-101242(R400)   ║
║ 0005___Dogiclon___912-101242 ║ Dogiclon ║ 912-101242 ║ MILT-R400          ║
╚══════════════════════════════╩══════════╩════════════╩════════════════════╝

TABLE inventory:

╔══════════════════════════════╦═══════╦═════════╦══════════╗
║             ref              ║ scost ║ instock ║ location ║
╠══════════════════════════════╬═══════╬═════════╬══════════╣
║ 0001___DOGICLON___912-101242 ║ 53.68 ║      24 ║ WA       ║
║ 0001___DOGICLON___912-101242 ║ 53.68 ║       0 ║ CA       ║
║ 0002___DOGICLON___912-101242 ║ 61.00 ║     121 ║ WA       ║
║ 0003___Dogiclon___912-101242 ║ 53.53 ║     100 ║ WA       ║
║ 0003___Dogiclon___912-101242 ║ 53.53 ║       0 ║ NY       ║
║ 0003___Dogiclon___912-101242 ║ 53.53 ║      20 ║ MA       ║
║ 0003___Dogiclon___912-101242 ║ 53.53 ║       2 ║ CA       ║
║ 0005___Dogiclon___912-101242 ║ 56.00 ║       5 ║ IN       ║
║ 0005___Dogiclon___912-101242 ║ 56.00 ║       5 ║ MA       ║
║ 0005___Dogiclon___912-101242 ║ 56.00 ║       5 ║ WA       ║
║ 0005___Dogiclon___912-101242 ║ 56.00 ║       5 ║ NY       ║
║ 0005___Dogiclon___912-101242 ║ 56.00 ║       2 ║ CA       ║
╚══════════════════════════════╩═══════╩═════════╩══════════╝

I guess pseduo code would be:

SHOW all products
WHERE 
    instock (any location) > 0 AND
    (cost > 10 AND cost < 2000)
ORDER BY
    cost asc

Notes:

refs are unique per supplier
brand and mpn lookup needs to be case insensitive

Expected Result:

╔══════════╦══════════╦════════════╦══════════════╦══════════════╦═══════════════════════════╗
║   ref    ║  brand   ║    mpn     ║     sku      ║    scost     ║          instock          ║
╠══════════╬══════════╬════════════╬══════════════╬══════════════╬═══════════════════════════╣
║ whatever ║ Dogiclon ║ 912-101242 ║ based on ref ║ based on ref ║ based on ref and location ║
╚══════════╩══════════╩════════════╩══════════════╩══════════════╩═══════════════════════════╝

This is what I'm trying:

SELECT DISTINCT
    product.ref,
    product.brand,
    inventory.scost,
    inventory.instock
FROM
    product
    JOIN inventory ON inventory.ref = product.ref
WHERE
    inventory.instock > 1 
    AND ( app.inventory.scost >= 10 AND app.inventory.scost <= 2000 ) 
GROUP BY
    product.ref

You need to show us some sample data and expected results because data is causing the results that are unexpected to you. My guess is that you have multiple records within the inventory table associated with a single product ref. — Shadow, Jun 11 '17 at 22:12
@Shadow Ah yes, product has no duplicates refs, but inventory does — eozzy, Jun 11 '17 at 22:13

score 0 · Accepted Answer · answered Jun 11 '17 at 22:19

0

If you are grouping by unique column within product which seems to be ref, distinct won't do what you wish it would. DISTINCT takes care of uniqueness per returned row (all values being returned). In this case to get one row in result per product ref you'd need to drop the distinct part, add product.mfr to group by and aggregate non-grouped columns like below:

SELECT
    product.ref,
    product.mfr,
    group_concat(inventory.scost) as scost,
    group_concat(inventory.instock) as instock,
FROM
    product
    JOIN inventory ON inventory.ref = product.ref
WHERE
    inventory.instock > 1 
    AND ( app.inventory.scost >= 10 AND app.inventory.scost <= 2000 ) 
GROUP BY
    product.ref, product.mfr

If you want to ensure proper ordering for scost and instock just include ordering clause within the group_concat itself ie:

group_concat(column order by column1, [...])

answered Jun 11 '17 at 22:19

Kamil Gosciminski

16,547
8
49
72

I don't understand why the negs on the question and your answer. That seemed to work, but I'm just learning MySQL so maybe I need to read more on group_concat and how it works. Thanks! – eozzy Jun 11 '17 at 22:29
@3zzy then let me explain. The question got downvotes because you failed to explain what the issue was therefore it is unclear what you asked. This answer got downvotes because it is a wild guess and if this wild guess is actually right, then the question is a duplicate and should be closed as such. – Shadow Jun 11 '17 at 22:37
@Shadow Fair enough, I just updated the description with all the details. I'm not sure whether this answer works for my case, but does return unique rows. – eozzy Jun 11 '17 at 22:59
@3zzy any aggregate function will return unique rows if applied on the inventory table's fields. – Shadow Jun 11 '17 at 23:31

JOIN two tables and GROUP by the common column

1 Answers1