first time here ...
I have an issue in MySQL:
I have a table 'products' with some fields like product_id, itemcode, label, price, .... There is also a second table 'inventory' that has some common fields with the first field. The field 'itemcode' is the easiest to compare both tables.
products has from every 'itemcode'-item only one appearance. Inventory, however, has them multiple times.
What I need to do: I need to output how many times each 'itemcode' from 'products' appears in 'inventory'. What would be the best way to do so?
example
products
itemcode
AA2244G
AB2245G
AC2246G
AD2247G
AE2248G
inventory
itemcode
AA2244G
AA2244G
AB2245G
AC2246G
AC2246G
AC2246G
AE2248G
Output
AA2244G 2x
AB2245G 1x
AC2246G 3x
AD2247G 0x
AE2248G 1x
SELECT a.itemcode, CONCAT(COUNT(b.itemcode), 'x') totalCount
FROM products a
LEFT JOIN inventory b
ON a.itemcode = b.itemcode
GROUP BY a.itemcode