i have 3 tables
table A with A.id …
table B with B.id, B.A_id
table C with C.id, C.A_id, C.type
table B and C can contain more records with A_id.
i need list of all record from A (regardless if there are records for A_id in table B or C),
record containing A_id in table B with max from B.id (B.id from the records containing A_id for every A.id),
record containing A_id in table C with type=o and max from C.id (C.id from the records containing A_id for every A.id)
thanks for the help, but i already tried that query and it does not return the correct results
the condition c.type = 0 is disregarded while max(c.id)
and if c.something is stated in the select c.something is not from the max(c.id) but from the first found record which is min(c.id)
select a.name,a.aid from
author a,catalog c where a.aid=a.aid
and c.bid in (select bid from orderdetails group by bid having(qty) in (select max(sum(qty)) from orderdetails having sum(qty) group by bid));
the error is ERROR 1111 (HY000): Invalid use of group function