Categories and Subcategories Help

[r937]

okay, you already have two WHERE clauses in there, one for each of the subqueries in your UNION

however, the specific request that you mentioned -- "just the apple row" -- takes a bit of finagling, if you're trying to put your query together in a scripting language

what you have to do is add this --

AND subcat.category_name = 'Apple'

to the second subquery's WHERE clause, and, as well, discard the entire first subquery of the UNION, because its job is to return the categories only, and 'Apple' is not a category, it's a subcategory

[daveporter]

Hi everyone,

I'm just looking at the sql discussed here & the one thing I don't quite understand is the use of:

cat.whatever and subcat.whatever in the select statement below. cat is not a table name or a field in the table, just wondering where it comes from ?

TIA - Dave Porter


select cat.categoryID
, cat.category_name
, null as subcategory_name
from categories as cat
where cat.parentID = 0
union all
select subcat.categoryID
, cat.category_name
, subcat.category_name as subcategory_name
from categories as cat
inner
join categories as subcat
on cat.categoryID = subcat.parentID
where cat.parentID = 0
order
by 2,3

[r937]

cat.whatever and subcat.whatever in the select statement below. cat is not a table name or a field in the table, just wondering where it comes from ?

those are table aliases

have another read of the sql, and be on the lookout for the word "as"

[spikeZ]

I would love to hear why do you advice him/her to do so though.

If I'm not mistaken ENUM is MySQL specific and if you were to migrate at some point to another database system eg Access it would need to be re-assigned where as CHAR wouldn't.