Getting NULL Value out of Max(Date) if no Data exists in joined table

Question

I want to see the latest approval date (V.Datum) of a certain item (BauteilID) coming from a distinct supplier (m.Lieferanz_FreigabeID). If there is not any approval date in the table related to a supplier, it should return NULL. Instead I get the latest approval date of other suppliers of the same item.

I tried to include IsNULL and IF conditions but it did not work.

    Select b.BauteilID, m.Lieferant_FreigabeID, Max(V.Datum) as Datum, m.Freigabe
    From WPLabor.dbo.MapBauteilStahlwerk m
    Join WPLabor.dbo.Bauteilspezifikation b  on b.BauteilID = m.BauteilID
    Join WPLabor.dbo.Verwendungsfreigabe v on v.BauteilID = b.BauteilID
    LEFT Join WPLabor.dbo.Stahlwerkzeugnis s on v.StahlwerkID = s.StahlwerkID And m.Stahlwerk = s. Stahlwerk
    group by b.BauteilID, m.Lieferant_FreigabeID, m.Freigabe

The result should be:

ID1, Supplier1, Datum1, Freigabe1
ID2, Supplier2, NULL, Freigabe2

Instead I get:

ID1, Supplier1, Datum1, Freigabe1
ID2, Supplier2, Datum1, Freigabe2

Help would by appreciated. Thanks in advance.

*If* all the values in the group are `NULL` then `NULL` will be returned. This implies that your `GROUP BY` clause is wrong, or your `JOIN`. — Thom A, Oct 14 '19 at 13:29

score 0 · Answer 1 · answered Oct 14 '19 at 16:43

You seem to be joining only on the Product ID and not the supplier and product which will result in returning the latest date of that product regardless of supplier.

Change:

Join WPLabor.dbo.Verwendungsfreigabe v on v.BauteilID = b.BauteilID

To something along the lines of:

Join WPLabor.dbo.Verwendungsfreigabe v on v.BauteilID = b.BauteilID AND v.SupplierID = b.SupplierID

Getting NULL Value out of Max(Date) if no Data exists in joined table

1 Answers1