Join baserad på två variabler

Hi

Jag har följande två tabeller:

Transactions
ID | Amount | year
1 | 100 | 2012
1 | -80 | 2011
2 | 100 | 2012
3 | 100 | 2012
3 | -50 | 2011

Invoice
ID | Number | year
1 | ABC123 | 2011
2 | DEF456 | 2012
3 | GHI789 | 2011
1 | JKL000 | 2012

Jag behöver en query som listar alla idnummer i <transactions> som inte går jämnt ut, samt dess tillhörande faktura baserad på ID-nummer och år. Tyvärr kan ID-nummer användas en gång per så denna kontroll måste göras. Queryn ska hämta fakturan från året på den tidigaste transaktionen. Se tex id 1. Denna har transaktioner både år 2011 och 2012. Då ska fakturan för år 2011 hämtas (ABC123) och inte den från 2012 (JKL000).

SELECT ID, sum(amount) FROM transactions
LEFT JOIN
ON transactions.id = invoice.id
AND transactions.year = invoice.year
GROUP by transactions.id
HAVING (sum(transactions.id)<>0)

Då får jag detta
1 100 JKL000
1 -80 ABC123
2 100 DEF456
3 100 NULL
3 -50 GHI789

Detta vill jag ha
1 20 ABC123
2 100 DEF4569
3 50 GHI789

Någon som har en klok ide på hur jag löser detta?

Hmm! Jag blir inte klok på hur id-systemet skulle kunna funka väl. I ditt exempeldata finns det ju två fakturor med id = 1, med olika år. Alltså har man ingen aning om "1 | 100 | 2012" tillhör ABC123 eller JKL000. Det känns inte stabilt, som "Dr Alban" skulle ha sagt i de ökända busringningarna.
Finns bara de kolumner som du listar ovan? Dvs, bara år, inget datum, samt inget eget ID för individuella transaktioner?

Hej, ID är i detta fall inte en primärnyckel. Tyvärr saknar vi primärnyckel till datan (är ett bokföringssystem där ID representerar verifikationsnummer).

Det går att kontrollera vilken transaktion som tillhör vilken faktura genom att para ihop år och ID. Av detta finns endast en kombination. Det är detta jag försöker åstadkomma, men som visas av resultat ovan går det inte så bra.

* Jag måste dels bygga listan så att endast en rad visas per IO samt saldot av total amount
* Dels hitta fakturanr för den aktuella transaktionen som fås ut av ID + det tidigaste registrerade året för den aktuella transen

Aha. Jag blev lurad av exempeldatat för GHI789, som verkade tyda på att en faktura kan sträcka sig över två år. Om år+id är unikt blir det hela genast enklare.

Något sådant här:

SELECT invoice.ID as ID, sum(amount) as BALANCE FROM invoice
LEFT JOIN transactions
ON transactions.id = invoice.id
AND transactions.year = invoice.year
GROUP by invoice.id, invoice.year
HAVING (sum(transactions.amount)<>0)

Men jag är fortfarande osäker. Å ena sidan säger du

Det går att kontrollera vilken transaktion som tillhör vilken faktura genom att para ihop år och ID. Av detta finns endast en kombination.

å andra sidan säger du

Queryn ska hämta fakturan från året på den tidigaste transaktionen. Se tex id 1. Denna har transaktioner både år 2011 och 2012. Då ska fakturan för år 2011 hämtas (ABC123) och inte den från 2012 (JKL000).

Om 2011/1 och 2012/1 är olika fakturor så vill du väl ha alla fakturor där balansen inte matchar, snarare än den tidigaste? Om 2011/1 går jämnt ut, men inte 2012/1, så vill du väl ha ut 2012/1?