help joining tables with group by [MYsql help]

[aymanstar]

have 3 tabels

1 for singer
2- for album
3-for songs and this tabel have field with name download that count how many download by song

i want to make states with top 10 in every section
in songs i make this code
[code]
<?php
$result=mysql_query("SELECT * FROM song order by download desc limit 10");
while($row=mysql_fetch_object($result)){
echo $row[name];
}
?>
[/code]
i want to make top 10 album by count the album songs download
and the same in singer
can you make an php example also

this my database
[code]

#
# Table structure for table `sing`
#

CREATE TABLE `sing` (
  `id` int(11) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL default '',
  `Description` text NOT NULL,
  `photo` text NOT NULL,
  `Category` varchar(255) NOT NULL default '0',
  `Site` varchar(255) NOT NULL default '',
  `fans` varchar(255) NOT NULL default '',
  `Country` varchar(255) NOT NULL default '',
  `Lyrics` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

# --------------------------------------------------------

#
# Table structure for table `song`
#

CREATE TABLE `song` (
  `id` int(10) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL default '',
  `mp3` varchar(255) NOT NULL default '',
  `rm` varchar(255) NOT NULL default '',
  `video2` varchar(255) NOT NULL default '',
  `video` varchar(255) NOT NULL default '',
  `remix` varchar(255) NOT NULL default '',
  `download` varchar(255) NOT NULL default '',
  `Category` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

# --------------------------------------------------------

#
# Table structure for table `tap`
#

CREATE TABLE `tap` (
  `id` int(10) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL default '',
  `addby` varchar(255) NOT NULL default '',
  `photo` varchar(255) NOT NULL default '',
  `Category` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
[/code]

[btherl]

Which columns match the sing and song tables?

Said another way, how do you know which singer sings which song?

[shvijay]

Please make sure that there should be relations between the tables on which you are trying to make join statement

[aymanstar]

i try but found error

not working

i try this code
[code]$result=mysql_query("select id,count(download) as songtotal from sing left outer join tap on sing.id=tap.Category left outer join song on song.Category=tap.id");
[/code]
and this code
[code]$result=mysql_query("SELECT a.id, b.Category, b.download, c.Category, FROM sing a RIGHT JOIN song b RIGHT JOIN tap c ON Category ORDER BY b.download desc limit 10");
[/code]
not working
i want 2 query
[list]
[*]1st query count caculate total album download from song tabel where Category in song is album id and the outbut is top10 album
[*]2nd query  count caculate total album download from song tabel where Category in song is album id and after that  display top 10 singer from sing tabel where sing.id=tap.Category 
[/list]