Select from two tables [message #18380] |
Tue, 29 January 2002 22:01 |
Fiesel
Messages: 3 Registered: January 2002
|
Junior Member |
|
|
Hi, i have some problems collecting data from two tables.
Table Lager:
Artikelnr
Buchbestand
Table Lagerjournal
Artikelnr
Buchungsdatum
I need all records from Lager which are matching following criteria:
Lager.Lagernr=1
Lager.Buchbestand=0
Lagerjournal.Buchungsdatum = more than 6 months ago
In the table Lagerjournal are a lot of records for each Artikelnr, so i need the record with the newest date.
My try so far (which is not working)
select J.Artikelnr,L.Lagernr,L.Buchbestand,J.Datum from Lager L, Lagerjournal J, (select max(Datum)max_date, Artikelnr from Lagerjournal group by Artikelnr)m where (L.Artikelnr = J.Artikelnr) and (J.Datum = m.max_date) and (L.Lagernr = 1) and (L.Buchbestand = 0);
I hope someone is able to understand my english and can help :-)
Fiesel
|
|
|