Hi John Kelly, This is not a perfect solution but may be useful to you. But still with two queries! From Mysql prompt issue these two queries.
SELECT @maxcat := max(Category) FROM table WHERE category = 'Sports:Football:Players' OR category = 'Sports:Football' OR category = 'Sports'; Select * from table where category = @maxcat; Note that "abc" is greater than "ab" This will not be affected by the depth of category. Anvar. At 06:15 PM 02/11/2001 -0800, you 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. > >Thanks! > > > >--------------------------------------------------------------------- >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 --------------------------------------------------------------------- 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
