CODEWITHSUNDEEP
sql
user1978340
user1978340

Reputation: 101

SQL Query to get 1 output from 2 different columns within same table

I am still new to SQL and was wondering what would be the best option to get distinct category names from two different columns from the same table.

Example: 

Table Name: Fruits

ID          CAT1           CAT2
1           APPLE          PEACH
2           PEACH          GRAPE
3           APPLE          GRAPE
4           ORANGE         APPLE
5           PEACH          PEAR

Desired Output

Distinct CAT
APPLE
PEACH
GRAPE
ORANGE
PEAR

I know that I would want to do a join where I name each table a letter like fruits a and fruits b so I match it via the ID but I cannot figure how to display it in one column only the distinct CAT from both columns.

Upvotes: 0

Views: 969

Answers (2)

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

You could query the distinct values of both columns separately and UNION (e.g. MySQL documentation) the results:

(SELECT DISTINCT CAT1 FROM Fruits)
UNION
(SELECT DISTINCT CAT2 FROM Fruits)

Upvotes: 3

Anup Shah
Anup Shah

Reputation: 1254

If you would have played with it little you would have get this already.

Select distinct cat from ( (Select cat1 as cat from fruits) union all (Select cat2 as cat from fruits)) q

Upvotes: 1

Related Questions