List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:February 8 2001 9:41am
Subject:SQL statement help please
View as plain text  
>>>>> "Phil" == Phil Latio <phil.latio@stripped> writes:

Phil> I hope somebody may of come across this sort of problem before dealing with
Phil> counting total records in subcategories.

Phil> I have two tables sslinks which contains links (URL) information and
Phil> sslinkcats which contains category information.

Phil> The category table "sslinkcats" has a field called "lcat_id" (which is an
Phil> autonumber) and also "lcat_cat" which is the "lcat_id" value of the parent
Phil> category. For example cricket would be under sport and the "lcat_cat" of
Phil> cricket would be "lcat_id" of sport.

Phil> The problem is that the below statement will only count the number of links
Phil> in each particular category.

Phil> $sql = "SELECT sslinkcats.lcat_id, COUNT(*) AS NumLinks
Phil>  FROM sslinks, sslinkcats
Phil>  WHERE link_cat = sslinkcats.lcat_id
Phil>  AND link_validated = 'yes'
Phil>  GROUP BY link_cat";

Phil> I want to display the total amount of links under the category sport to
Phil> include all subcategories like football,cricket,cricket rugby. Also if
Phil> cricket had subcategories, then I would these counted too.

Phil> I am sure the answer is looking at me but I just can't see it. Therefore any
Phil> pointers or assistance would be very welcome.

This is a list dedicated to discussions about MySQL internals;  Please
redirect questions like this to mysql@stripped

SQL statement help pleasePhil Latio8 Feb
  • SQL statement help pleaseMichael Widenius8 Feb