category | manufacturer
baseball | adidas
hockey | nike
football | reebok
soccer | nike
i'm trying to query out all the distinct manufacturers for each category.
i was trying to use this select statement, which isn't working... and i'm not sure if this is just because my query is wrong, or my version of mysql (4.0.15-standard) doesn't support sub-selects.
here is the horrible query which is probably way off !
select distinct category from table where exists (select distinct manufacturer from table)
This is just how recordsets are presented. It would be up to you to write the code to parse them out to how you want them presented in your web page. Setting a local variable for the current manufacturer name and then watching for it to change to a new one as you loop through the recordset would be the standard way.
It would be possible to return a two field recordset instead like so...
select distinct(category), manufacturer from table;
I guess my real question is why would the same category be under a given manufacturer more than once? This would be the only reason to need to use distinct(). Unless of course the table you have shown is not the entire table?
I would declare manufacturer/category as a unique key then there would be no duplicates.