user1170330 user1170330 - 7 days ago 6
MySQL Question

Combine multiple distinct values

I have a table with the following structure:

| animal | color |
|--------|-------|
| dog | black |
| dog | white |
| cat | white |
| dog | black |
| mouse | grey |


I now want to get the distinct values of each column.

Just doing:

SELECT DISTINCT animal, color from tab1


Would just omit the 2nd occurrence of
dog - black
:

| animal | color |
|--------|-------|
| dog | black |
| dog | white |
| cat | white |
| mouse | grey |


But what I want is some structure that looks as follows:

animal: dog, cat, mouse
color: black, white, grey





My approach would just be to perform several SELECT queries:

- SELECT DISTINCT animal from tab1
- SELECT DISTINCT color from tab1


And then just combine those results into an array with PHP.

But is there a quicker way, maybe with just one query?

Answer

Yes, your second approach is right, but you can do it using SQL:

SELECT DISTINCT `animal` from `tab1` UNION SELECT DISTINCT `color` from `tab1`

A good thing about UNION is, it already filters your values. So, you can get rid of DISTINCT leaving you with:

SELECT `animal` from `tab1` UNION SELECT `color` from `tab1`

But in this case, you won't be able to differentiate which is from color and which is from animal. You may add a separator SELECT and use it to differentiate.

Comments