distinct values from multiple columns?

[schilly]

If I have a table of items with two columns set for categories, say cat1 and cat2 so I can list an item in multiple categories.

 

What's the best way to get a list of all the unique categories? Some categories may only be listed in cat1 and some only in cat2 and some in both.

 

ex.

 

cat1 | cat2

cat | dog

house | apartment

dog | cat

 

What query would I use to return:

 

cat

house

apartment

dog

 

(no particular order)

 

I'm trying to use combinations of DISTINCT but it doesn't seem to work.

 

 

 

[otuatail]

You need to reserch your table layout. Why can cat be in category 1 and somwhere else be in category 2.  Question! how is this table being populated? by user input?

 

Desmond.

 

[schilly]

Thanks for the reply. It's not user inputted.

 

well say you have a primary and secondary categories. one product will want to be listed in cat A as their primary but another product will want to be listed in cat A as their secondary giving you the same category value in both columns.

[DavidAM]

SELECT DISTINCT cat1 FROM table
UNION DISTINCT
SELECT DISTINCT cat2 FROM table

 

something like that, I'm not sure if all of the DISTINCTs are needed

[schilly]

SELECT DISTINCT cat1 FROM table
UNION DISTINCT
SELECT DISTINCT cat2 FROM table

 

something like that, I'm not sure if all of the DISTINCTs are needed

 

awesome. just what i needed. thanks so much.