[sql] Laagste waarde selecteren

[Dennis]

Goeiedag,

Volgens mij is het antwoord vrij simpel, maar het is me even ontschoten!

Ik heb drie tabellen. Een tabel met een betaling, een tabel met een betalingsstatusgeschiedenis (hiervan kunnen meerdere record per betaling zijn) en een tabel met de verschillende statussen die mogelijk zijn. Nou wil ik graag alle betalingen en daarbij de meest recente betalingsstatus ophalen in één query. Zou zit ik te pielen met de GROUP BY en de aggregratie functies, maar volgens mij heb ik het toch verkeerd.

Dit heb ik nu, maar dat returnt alle rijen in de betalingsstatusgeschiedenis. Dat moet dus niet .

code:

SELECT
    p.Id, 
    p.MemberId, 
    p.BankAccountNumber, 
    p.Amount, 
    p.NotReceiveBefore, 
    m.FirstName, 
    m.BetweenName, 
    m.LastName, 
    ph.PaymentStatusDate, 
    ps.Name
FROM
    Payment AS p, 
    Member AS m, 
    PaymentHistory AS ph, 
    PaymentStatus AS ps
WHERE
    m.Id = p.MemberId AND
    ph.PaymentId = p.Id AND
    ps.Id = ph.PaymentStatusId
ORDER BY
    ph.PaymentStatusDate DESC

Kan iemand mij een zetje in de goede richting geven?

[MrNGm]

Je bent ps.Id, m.Id, ph.PaymentId, ph.PaymentStatusId vergeten in het SELECT gedeelte. Afaik moet dat er ook bij staan, als je iets in de WHERE clause wilt gebruiken.

code:

SELECT
    p.Id, p.MemberId, p.BankAccountNumber, p.Amount, p.NotReceiveBefore, 
    m.Id, m.FirstName, m.BetweenName, m.LastName, 
    ph.PaymentStatusDate, ph.PaymentStatusId, ph.PaymentId,
    ps.Name, ps.Id
FROM
    Payment AS p, 
    Member AS m, 
    PaymentHistory AS ph, 
    PaymentStatus AS ps
WHERE
    m.Id = p.MemberId AND
    ph.PaymentId = p.Id AND
    ps.Id = ph.PaymentStatusId
ORDER BY
    ph.PaymentStatusDate DESC

Zoiets?

[Tukk]

Ik neem aan dat er in de table PaymentHistory een datum veld zit (paymentstatusdate?), als je in een sbquery daar de meest recente record ophaalt dan ben je der toch?

iets als:

SQL:

SELECT
    p.Id, 
    p.MemberId, 
    p.BankAccountNumber, 
    p.Amount, 
    p.NotReceiveBefore, 
    m.FirstName, 
    m.BetweenName, 
    m.LastName, 
    ph.PaymentStatusDate, 
    ps.Name
FROM
    Payment AS p, 
    Member AS m, 
    
    PaymentStatus AS ps
WHERE
    m.Id = p.MemberId AND
    ph.PaymentId = p.Id AND
    ps.Id = (Select ph.PaymentStatusId 
                   from PaymentHistory AS ph 
                 where rowcount =1 
                   order by ph.paymentstatusdate)
ORDER BY
    ph.PaymentStatusDate DESC

[ Voor 4% gewijzigd door Tukk op 24-12-2006 11:50 ]

[analog_]

code:

SELECT max(ph.PaymentStatusDate),ps.Name 
FROM Payment p
INNER JOIN PaymentHistory ph ON ph.PaymentId = p.Id
INNER JOIN PaymentStatus ps ON ps.ID = ph.PaymentStatusId
INNER JOIN Member m ON p.MemberId = m.Id
GROUP BY p.Id

Zou het zoiets kunnen zijn ?

[Dennis]

MrNGm schreef op zondag 24 december 2006 @ 11:48:
Je bent ps.Id, m.Id, ph.PaymentId, ph.PaymentStatusId vergeten in het SELECT gedeelte. Afaik moet dat er ook bij staan, als je iets in de WHERE clause wilt gebruiken.

[...]

Zoiets?

Nee, dat is niet nodig .

Tukk schreef op zondag 24 december 2006 @ 11:49:
Ik neem aan dat er in de table PaymentHistory een datum veld zit (paymentstatusdate?), als je in een sbquery daar de meest recente record ophaalt dan ben je der toch?

Dat klopt! Dat is inderdaad het veld dat je noemt.

iets als:

SQL:

...

Het probleem is dat die query niet matcht met de name. Dat is erg rot aan deze query, omdat je niet kunt groeperen op het ps.name veld (die kan immers best meerdere keren hetzelfde zijn).

Mr.SiS schreef op zondag 24 december 2006 @ 11:49:

[...]

Zou het zoiets kunnen zijn ?

Die haalt alleen de verschillende statussen op, maar niet de betalingen, die ik toch wel graag wil hebben . Als ik het veld p.Id vervolgens toch toevoeg krijg ik alsnog drie rijen terug, terwijl het er juist één moet zijn .

Iedereen alvast bedankt voor de hulp.

[MacWebber]

Er vanuitgaande dat je betalings historie niet wijzigt (dat doet de meeste betalingshistorie niet), zou je waarschijnlijk met een

SQL:

select max(id) from paymenthistory where idpayment = xx

het meest recente record op kunnen halen. Die kun je dan in de where van je oorspronkelijke query gebruiken.

[Dennis]

MacWebber schreef op zondag 24 december 2006 @ 14:07:
Er vanuitgaande dat je betalings historie niet wijzigt (dat doet de meeste betalingshistorie niet), zou je waarschijnlijk met een

SQL:

select max(id) from paymenthistory where idpayment = xx

het meest recente record op kunnen halen. Die kun je dan in de where van je oorspronkelijke query gebruiken.

Yep, dat lukt inderdaad! Met een subquery dus . Ik had er al aan gedacht maar dacht dat het ook zonder subquery zou moeten mogen.

SQL:

SELECT
    p.Id, 
    p.MemberId, 
    p.BankAccountNumber, 
    p.Amount, 
    p.NotReceiveBefore, 
    m.FirstName, 
    m.BetweenName, 
    m.LastName, 
    ph.PaymentStatusDate, 
    ps.Name
FROM
    Payment AS p, 
    Member AS m, 
    PaymentHistory AS ph, 
    PaymentStatus AS ps
WHERE
    m.Id = p.MemberId AND
    ph.PaymentId = p.Id AND
    ps.Id = ph.PaymentStatusId AND
    ph.PaymentStatusDate = (
        SELECT 
            MAX(PaymentStatusDate)
        FROM
            PaymentHistory
        WHERE
            PaymentId = p.Id
        )
ORDER BY
    ph.PaymentStatusDate DESC

[Woy]

Ik heb je post niet helemaal goed doorgelezen maar kan je niet iets met HAVING doen?

[DexterDee]

Het kan ook zonder subquery, door te sorteren op betaaldatum en dan een limit 1 te doen om alleen de meest recente terug te geven.

[robbert]

DexterDee schreef op zondag 24 december 2006 @ 19:35:
Het kan ook zonder subquery, door te sorteren op betaaldatum en dan een limit 1 te doen om alleen de meest recente terug te geven.

En als er nou meerdere betalingen op die datum waren? Dan krijg je er maar 1....

[DexterDee]

robbert schreef op zondag 24 december 2006 @ 19:42:
[...]

En als er nou meerdere betalingen op die datum waren? Dan krijg je er maar 1....

Als ik de TS lees is hij op zoek naar de laatste betaalstatus. Dat is er altijd maar 1 lijkt me

[MacWebber]

Yep, dat lukt inderdaad! Met een subquery dus . Ik had er al aan gedacht maar dacht dat het ook zonder subquery zou moeten mogen.

SQL:

        SELECT 
            MAX(PaymentStatusDate)
        FROM
            PaymentHistory
        WHERE
            PaymentId = p.Id

Psies. De reden waarom ik het overigens op ID zou doen is om te voorkomen dat je meerdere records krijgt als er op 1 datum meerdere statussen zijn (niet helemaal ondenkbaar). Als je statusdate ook een tijdsdeel bevat wordt die kans uiteraard al aanzienlijk kleiner.