mysql query help needed

[kiprijan]

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)

[swampBoogie]

select a.id,
       max(b.id) as maxbid, 
       max(c.id) as maxcid
  from a
  left join b
    on a.id = b.a_id
  left join c
    on a.id = c.a_id
   and c.type = 0
 group by a.id

[kiprijan]

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)

[kiprijan]

sorry my bad about the type=0 disregarding (this happens if i join the table C table with table D and the d.type=0 - but this is not the problem)

the query returns the c.id but c.something is incorrect

[r937]

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)

yeah, but there was no c.something in your original post, you just added it

and of course the query no longer works the way you want if you add it

[srinathn1]

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

please help

[r937]

please help

sure

do you know what the GROUP BY clause does?