Howto get newest dataset with SQL join?

Question

Hi,

I have got the following join:

SELECT  l.cFirma AS Lieferant,
        SUM(la.fEKNetto) AS Verbindlichkeiten,
        l.kLieferant AS Lieferanten_ID,
        100 - gk1.fFaktor * 100 AS Grundkondition,
        MAX(gk1.dDatum) AS Datum
FROM    tBestellung b, tArtikel a, tBestellpos p, tLieferant l, tLiefArtikel la, tGrundkondition gk1
WHERE   
        CAST('01.01.2010' AS DATETIME) <= CAST(b.dErstellt AS DATETIME)
        AND b.cType = 'B' 
        AND p.tBestellung_kBestellung = b.kBestellung
        AND a.kArtikel = p.tArtikel_kArtikel
        AND l.kLieferant = la.tLieferant_KLieferant
        AND a.kArtikel = la.tArtikel_kArtikel
        AND gk1.tLieferant_kLieferant = l.kLieferant
GROUP BY l.kLieferant, cFirma, gk1.fFaktor
ORDER BY Verbindlichkeiten DESC, Lieferant

Please fokus on the table "tGrundkondition" alias gk1. There is a DATETIME column called "dDatum" and a foreign key "tLieferant_kLieferant".

Now I need only the latest data from this table, joined with the other stuff. I already used the MAX(gk1.dDatum) function, but I still get all entries of gk1. I need only the latest (with the highest dDate). Actually I don't need to output the date but only to filter the data.

I'm running this statement on Microsoft SQL Server via ODBC. Do you need any further information?

I hope you can help me. Thanks in advance.

Answer 1

You need to use a correlated subquery, for example add the following:

WHERE gk1.DATUM = (SELECT MAX(SUB.DATUM) FROM tGrundkondition SUB
    WHERE SUB.tLieferant_kLieferant = l.kLieferant)

I am not sure this is 100% correct because I don't know your table structure, but it should give you an idea.

Answer 2

Try to do something like this:

SELECT  l.cFirma AS Lieferant,
        SUM(la.fEKNetto) AS Verbindlichkeiten,
        l.kLieferant AS Lieferanten_ID,
        100 - gk1.fFaktor * 100 AS Grundkondition,
        gk1.dDatum AS Datum
FROM    tBestellung b, tArtikel a, tBestellpos p, tLieferant l, tLiefArtikel la, tGrundkondition gk1
WHERE   
        CAST('01.01.2010' AS DATETIME) <= CAST(b.dErstellt AS DATETIME)
        AND b.cType = 'B' 
        AND p.tBestellung_kBestellung = b.kBestellung
        AND a.kArtikel = p.tArtikel_kArtikel
        AND l.kLieferant = la.tLieferant_KLieferant
        AND a.kArtikel = la.tArtikel_kArtikel
        AND gk1.tLieferant_kLieferant = l.kLieferant
        AND gk1.dDatum = (SELECT MAX(dDatum) from _ITS TABLE_)
GROUP BY l.kLieferant, cFirma, gk1.fFaktor
ORDER BY Verbindlichkeiten DESC, Lieferant

I don't know if it works on SQL SERVER.... but I used a lot on DB2

Answer 3

SELECT  l.cFirma AS Lieferant,
        SUM(la.fEKNetto) AS Verbindlichkeiten,
        l.kLieferant AS Lieferanten_ID,
        100 - gk1.fFaktor * 100 AS Grundkondition,
        gk1.dDatum AS Datum
FROM    (
        SELECT  TOP 1 *
        FROM    tGrundkondition
        ORDER BY
                dDatum DESC
        ) gk1,
        tBestellung b, tArtikel a, tBestellpos p, tLieferant l, tLiefArtikel la
WHERE   
        CAST('01.01.2010' AS DATETIME) <= CAST(b.dErstellt AS DATETIME)
        AND b.cType = 'B' 
        AND p.tBestellung_kBestellung = b.kBestellung
        AND a.kArtikel = p.tArtikel_kArtikel
        AND l.kLieferant = la.tLieferant_KLieferant
        AND a.kArtikel = la.tArtikel_kArtikel
        AND gk1.tLieferant_kLieferant = l.kLieferant
GROUP BY l.kLieferant, cFirma, gk1.fFaktor
ORDER BY Verbindlichkeiten DESC, Lieferant