On Fri, Nov 02, 2001 at 06:15:08PM -0800, John Kelly wrote:
> Hi, I have a MySQL table with a column that contains some of a web site
> directory's category names in the format:
>
> Sports:Football:Players
>
> I am trying to build a query that that locates all records that match the
> above category name OR if none exist its parent "Sports:Football" OR if none
> exist its parent "Sports". The top level category, in this case "Sports",
> will always have at least one matching record.
>
> I know I can do this with multiple queries by checking the previous query's
> result, but I am trying to build a query that does it all in one lookup to
> avoid lots of lookups in deep categories. Something along the logical lines
> of ...
>
> SELECT * FROM table WHERE category = 'Sports:Football:Players' OR IF NONE
> category = 'Sports:Football' OR IF NONE category = 'Sports'
>
> ... of course the above query does not work but if anyone knows of how to
> accomplish something similar in one query I would much appreciate it.
Sir, the problem is that relational databases use set logic, and you
are trying to find a solution in sequential logic.
You need three tables. I'll call them Cat1, Cat2, and Cat3, but you
should rename them to whatever makes sense to you. They will hold,
respectively, 'Sports', 'Football', and 'Players'. (Or 'Business',
'Industry', and 'Firms'. Or whatever.)
The table structure would be something like:
Cat1(Cat1ID, Category)
Cat2(Cat1ID, Cat2ID, Category)
Cat3(Cat2ID, Cat3ID, Category)
A sample row in Cat1 would be (1, 'Sports')
A sample row in Cat2 would be (1, 1, 'Football')
A sample row in Cat3 would be (1, 1, 'Players')
(If you're not concerned about economizing on storage, you could
eliminate the IDs and have each child record refer to the parent
category instead of the parent ID. In that case, you don't need
the Cat1 table, which serves only to match a category with its
ID.)
The query would be
SELECT Cat1.Category, Cat2.Category, Cat3.Category
FROM (Cat1 LEFT JOIN Cat2 ON Cat1.Cat1ID = Cat2.Cat1ID)
LEFT JOIN Cat3 ON Cat2.Cat2ID = Cat3.Cat2ID
WHERE Cat1.Category = 'Sports'
AND (Cat2.Category = 'Football' OR Cat2.Category IS NULL)
AND (Cat3.Category = 'Players' OR Cat3.Category IS NULL);
If you want the output in the 'Sports:Football:Players' form, you
can rewrite the SELECT clause as
SELECT Concat(Cat1.Category,
If(IsNull(Cat2.Category), '', ':'), Cat2.Category,
If(IsNull(Cat3.Category), '', ':'), Cat3.Category)
Disclaimer: I haven't run this, so I might have gotten some of the
details wrong.
Bob Hall
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
