datetime - MySQL Get latest record by date from mutiple tables with values from lookup tables -

i want latest mainnumber, serial, bartype , notes given mainnumber, if exist. note bartype stored in lookup table , referenced barid.

unreason came this:

select @mainnumber, coalesce(n.notes, 'none') numbers       left join notes n on numbers.mainnumber = n.mainnumber      left join notes n2 on n.mainnumber = n2.mainnumber , n.date < n2.date n2.date null , numbers.mainnumber = @mainnumber 

this fine whether notes null or not, need serial , bartype. mainnumber may have been assigned multiple serials during lifetime, want latest serial. (i'll need 15 other fields in other tables, performant answer appreciated possible)

tables

numbers table:

create table `numbers` (   `id` int(10) unsigned not null auto_increment,   `mainnumber` varchar(11) not null,   `serial` varchar(20) not null,   `date` timestamp not null default current_timestamp on update current_timestamp,   primary key  (`id`),   unique key `serial` (`serial`) ) engine=myisam auto_increment=460 default charset=latin1 

notes table:

create table `notes` (   `id` int(10) unsigned not null auto_increment,   `mainnumber` varchar(11) not null,   `notes` longtext not null,   `date` timestamp not null default current_timestamp on update current_timestamp,   primary key  (`id`),   key `mainnumber` (`mainnumber`) ) engine=myisam auto_increment=11 default charset=latin1 row_format=dynamic 

ref_bars table:

create table `ref_bars` (   `barid` varchar(6) not null,   `bartype` varchar(30) not null,   primary key  using btree (`barid`) ) engine=myisam default charset=latin1 

bars table:

create table `bars` (   `id` int(10) unsigned not null auto_increment,   `mainnumber` varchar(11) not null,   `barid` varchar(6) not null,   `date` timestamp not null default current_timestamp on update current_timestamp,   primary key  (`id`),   key `mainnumber` (`mainnumber`) ) engine=myisam auto_increment=212 default charset=latin1 row_format=dynamic 

sample data

select * notes:

'id','mainnumber','notes','date' '1','1','repaired','2009-03-23 12:00:00' '2','1','replaced','2009-08-15 19:20:05' 

note: 2 rows mainnumber = 1, no row mainnumber of 2. ids technical , never used.

select * numbers:

'id','mainnumber','serial','date' '1','1','4642785154854','2008-08-15 12:30:00' '2','1','4642315642316','2009-08-15 12:50:00' '3','2','5412558456223','2010-08-15 11:30:00' 

select * bars:

'id','mainnumber','barid','date' '1','1',1,'2008-08-15 12:30:00' '2','1',2,'2009-08-15 12:50:00' '3','2',2,'2010-08-15 11:30:00' 

select * ref_bars:

'barid','bartype' '1','banned' '2','payment required' 

expected output

mainnumber = 1

mainnumber,notes,banned,unpaid '1','replaced','yes','yes' 

mainnumber = 2

mainnumber,notes,banned,unpaid '2','none','no','yes' 

edit: fixed , tested it, whilst making things clearer (hopefully). rushed off other things earlier today, sorry wasting people's time badly-written, incomplete question.

updated clarify more complex requirements

you can join, suggested unreason.

another way subquery:

select distinct s.mainnumber,  coalesce(   (select n.notes notes n n.mainnumber=s.mainnumber order n.date desc limit 1),    'none') lastnote numbers s s.mainnumber=? 

note solution using join may or may not perform better, you'll have try it.

also note "limit" mysql-specific (not ansi sql), take care if intend migrate dbms.