joining tagbles and sorting

Hi


I have three tables ..
The one is for all the categories
The other is for all the partner branches of the categories
the third is for tracking when the partner was accessed and also counts how many times accessed
Your simple tree structure.

Tables:

1: partnercategory
categoryid
categoryname

2: Partner
partnerid
categoryid
name
redirectpage

3 : access
partnerid
accessdatetime

I can do it by coding something in php but is their an easier way with mysql query ie.

SELECT
partnercategory.categoryname AS cat,
partner.name AS partner,
partner.redirectpage AS page,
count(access.accessdatetime) AS popular
FROM
partner
LEFT JOIN
?
LEFT JOIN
?
WHERE ? ORDER BY popular


Thanks for any help
Louis

Thanks

$sql1 = "SELECT
partnercategory.categoryname AS cat,
partner.name AS partner,
partner.redirectpage AS page,
count(access.accessdatetime) AS popular
FROM
(partner LEFT JOIN access
ON partner.partnerid = access.partnerid)
LEFT JOIN partnercategory
ON partnercategory.categoryid = partner.categoryid
GROUP BY partner ORDER BY popular DESC
";
That worked I dont need to limit, and I had to add the group by

this also works
$sql1 = "SELECT partnercategory.categoryname, partner.name,
partner.redirectpage, count(*) as count FROM partnercategory, partner,
access WHERE partnercategory.categoryid = partner.categoryid AND
access.partnerid = partner.partnerid GROUP BY partner.name order by count
DESC";



"Nikolai Chuvakhin" <nc@iname.com> wrote in message
news:32d7a63c.0308011217.4b4db36@posting.google.co m...
> "louiss" <louiss@equation.co.za> wrote in message
> news:<bgda0a$m8l$1@ctb-nnrp2.saix.net>...
> >
> > I have three tables ..
> > The one is for all the categories
> > The other is for all the partner branches of the categories
> > the third is for tracking when the partner was accessed and also
> > counts how many times accessed
> > Your simple tree structure.
> >
> > Tables:
> >
> > 1: partnercategory
> > categoryid
> > categoryname
> >
> > 2: Partner
> > partnerid
> > categoryid
> > name
> > redirectpage
> >
> > 3 : access
> > partnerid
> > accessdatetime
> >
> > I can do it by coding something in php but is their an easier way with
> > mysql query
>
> Let's say you want to fetch ten most popular partners:
>
> SELECT
> partnercategory.categoryname AS cat,
> partner.name AS partner,
> partner.redirectpage AS page,
> count(access.accessdatetime) AS popular
> FROM
> (partner LEFT JOIN access
> ON partner.partnerid = access.partnerid)
> LEFT JOIN partnercategory
> ON partnercategory.categoryid = partner.categoryid
> ORDER BY popular DESC
> LIMIT 10;
>
> Cheers,
> NC