query within query

[David White]

Hi fellow Martians

I have two tables STATs (statistics) and SITEs (websites).
The first STATs contains a series of records that are in date order, the second SITEs the list of websites.

I want to list the sites with the oldest stats first.

The following works badly in that at least it shows the last date that STATs were added for each site but in the wrong site order:

select
cp_SITEs.site_id,
cp_SITEs.site_name, cp_SITEs.URL,
cp_STATs.gpages,cp_STATs.glinks,
cp_STATs.ypages, cp_STATs.ylinks,
cp_STATs.mpages, cp_STATs.mlinks,
DATE_FORMAT(cp_STATs.date,'%d %M %Y') as statdate

from
cp_SITEs, cp_STATs

where
cp_SITEs.current=0 and cp_SITEs.parent_id=$account_id
and
cp_SITEs.URL != ''
and
cp_SITEs.site_id= cp_STATs.site_id

order by
cp_STATs.site_id asc, cp_STATs.date desc


It seems the more I stare the less I can see!

Replies appreciated.

David