CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jase
Jase

Reputation: 91

How to join records from a group by

I don't know if the title is as descriptive as I wanted but I'll try to explain with real examples of what I want.

In my table 'Details' I have 

   Date   |   ProductId   |   Total   
-------------------------------------
17/05/20  |     16788     |    62
--------------------------------------
19/05/20  |     3789      |    15

So I want the result be something like that:

17/05/20 - 16788 - 62
17/05/20 - 3789  - NULL (or 0) 
19/05/20 - 16788 - NULL (or 0)
19/05/20 - 3789  - 15

I started doing RIGHT JOIN with a GROUP BY of the Dates, but didn't work. I run out of ideas, can someone help me?

Thanks in advance

Upvotes: 1

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can generate the rows with a cross join and then bring in the values using left join:

SELECT d.date,
       p.productid,
       t.total
FROM   (
           SELECT DISTINCT DATE
           FROM   details
       ) d
       CROSS JOIN (
                SELECT DISTINCT productid
                FROM   details
            ) p
       LEFT JOIN details t
            ON  t.date = d.date
            AND t.productid = p.productid
ORDER BY
       d.date,
       p.productid DESC;

Upvotes: 2

Related Questions