here is my one table:


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)


any insight would be greatly appreciated.
tia !

Are you trying to get the manufacturers or categories? You say manufacturers, but then your query is written to "SELECT category".

Can you be more specific about what your result set would look like?

duh... sorry !

i'm trying to list out all unique categories for each manufacturer... for example:


nike
===
hockey
baseball
soccer

adidas
===
hockey
baseball
football

Okay, here's a couple of solutions. First, if you want all the categories for a single manufacturer, you would...

SELECT category FROM table WHERE manufacturer = '<manufacturer>'

...which would give you...

<category1>
<category2>
<category3>

----------------------------------------------------

I'm assuming you want something more complete...

SELECT manufacturer, category FROM table ORDER BY manufacturer, category

...giving you...

<manufacturer1> | <category1>
<manufacturer1> | <category2>
<manufacturer1> | <category3>
<manufacturer2> | <category1>
<manufacturer2> | <category3>

...assuming <manufacturer2> is not associated with <category2>, and so on and so on and so on...

-----------------------------------------------------

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...

<manufacturer1> | 'manufacturer'
<category1> | 'category'
<category2> | 'category'
<category3> | 'category'
<manufacturer2> | 'manufacturer'
<category1> | 'category'
<category3> | 'category'

...so all you have to know is whether the first field is a 'manufacturer' or 'category', but this is generally more trouble than it's worth.

Hope this has helped.

cipherlad, that made perfect sense. thank you very, very much for breaking that down into english

could always do:

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.