sql - Subquery problems -

i have 2 tables, catch , members. it´s af fishing club website. want make @ list showing name, weight, spiecies. top weight each spiecies, rekord list. 2 tables looks this.

members:

• memberid
• firstname
• secondname

catch:

• memberid(fk)
• spiecies
• weight

this shows name , weight, can´t spiecies shown.

select   concat(firstname, ' ' ,secondname) 'name', allcatch.rekord members join (select  max(weight) 'rekord', memberid catch  group memberid) allcatch on allcatch.memberid = members.memberid;   -- order allcatch.weight desc 

try this:

select   concat(firstname, ' ' ,secondname) 'name', allcatch.rekord, allcatch.spiecies members inner join catch on catch.memberid = members.memberid inner join (select  max(weight) 'rekord', spiecies catch  group spiecies) allcatch on allcatch.rekord = catch.weight ,  allcatch.spiecies = catch.spiecies 

.