CODEWITHSUNDEEP
sqlsql-servert-sqljoingroup-by
artielegancko
artielegancko

Reputation: 21

SQL - select values from two different tables

I have got a problem with selecting values from two different tables. Tables below:

Material:

MaterialID | MaterialName
-------------------------
1111111    | Material1
2222222    | Material2
3333333    | Material3

Stock:

MaterialID | Location | Quantity
---------------------------------
1111111    | LocA     | 10
1111111    | LocB     | 20
2222222    | LocC     | 15
2222222    | LocD     | 10

My SQL query below:

SELECT
    [Material].[MaterialName] as 'Material Name',
    custom.quantity as 'Total Quantity',
FROM
    [Material]
    inner join (
        SELECT
            [MaterialId] as 'Materialcode',
            SUM([Quantity]) as 'Quantity'
        from
            [Stock]
        group by
        [MaterialId]
    ) custom 
    on 
        custom.Materialcode = [Material].[MaterialId]

The result is:

Material Name | Total Quantity
------------------------------
Material1     | 30
Material2     | 25

The problem is that in the result there is no information about Material3 (I know that the quantity is equal to 0 as it`s not in Stock table, but I need a result that will show all of the materials - like below:

Material Name | Total Quantity
------------------------------
Material1     | 30
Material2     | 25 
Material3     | 0

Is it possible?

Upvotes: 0

Views: 57

Answers (1)

GMB
GMB

Reputation: 222462

You can left join and aggregate:

select m.materialName, coalesce(sum(s.quantity), 0) total_quantity
from material m
left join stock s on s.materialID = m.materialID
group by m.materialID, m.materialName

You may also aggregate, then left join (that was your original attempt, you just need to change the join type).

Actually, you might as well use a correlated subquery - with an index on stock(materialID, quantity), this may be an efficient solution (and you don't need coalesce() here):

select 
    m.materialName,
    (select sum(quantity) from stock s where s.materialID = m.materialID) total_quantity
from material m

Another way to express this is to use a lateral join:

select m.materialName, s.total_quantity
from material m
outer apply (select sum(quantity) total_quantity from stock s where s.materialID = m.materialID) s

Upvotes: 2

Related Questions