mysql - Query sql to find row's count from another table -

in mysql db have these 3 tables:

account (primary key name)  name  directory  birthdate  sex  files (primary key id)  id (autoincrement)  name (account foreign key)  filename  uploaded_date  checked (primary key couple name - filename)  name (account foreign key)  filename (files foreign key) 

i want make query shows resulting table this:

result  name - directory - birthdate - sex - filename uploaded_date - checkedcount 

where in checkedcount column want have existing row's count number in checked table every unique filename.

i tried had no success:

select * (select * account natural join files) table_alias left join (select *, count(*) checkedcount checked) checked_alias on table_alias.filename = checked_alias.filename 

currently in account table there 2 row, in files table 6 rows , in checked tables there 6 rows same name account , others 6 unique filenames files.

i can't understand why in resulting table in checkedcount columns have values setted null, except 1 setted 6. goal should have 6 rows checkedcount = 1 since every file in files table used 1 times in check table.

try this

select     a.*, f.filename, f.uploaded_date, ifnull(c.cnt, 0) 'checkedcount'     account     left outer join files f on a.name = f.name     left outer join (select name, count(*) checked group 1) c on a.name=c.name