CODEWITHSUNDEEP
mysqlsql
Guesser
Guesser

Reputation: 1857

MYSQL - Using GROUP with JOIN

So this statement started off without the join, it would simply lookup the product tables and group results by item_code.

SELECT a.item_code,
       a.price,
       b.v_no
FROM product a
JOIN variation b ON a.item_code = b.item_code
WHERE a.price>=2
GROUP BY a.item_code

Now that I have added the join I want to group by v_no but ONLY if data v_no exists. This is because not every item from the product table has a corresponding entry in the variation table. So group by item_code for 'normal' products, but group by v_no THEN item_code if a v_no exists.

The below does not work:

SELECT a.item_code,
       a.price,
       b.v_no
FROM product a
JOIN variation b ON a.item_code = b.item_code
WHERE a.price>=2
GROUP BY b.v_no,
         a.item_code

The item_code grouping remains but the v_no grouping is ignored.

Upvotes: 1

Views: 35

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • In order to consider all the product(s) (with/without variations), you will need to change to LEFT JOIN.
  • For conditional Group By, you can use Coalesce() function; it will consider the first non-null value for Grouping upon.

Try the following:

SELECT a.item_code,
       a.price,
       b.v_no
FROM   product a
       LEFT JOIN variation b
         ON a.item_code = b.item_code
WHERE  a.price >= 2
GROUP  BY COALESCE(b.v_no, a.item_code)

Upvotes: 1

Related Questions