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

Question

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.

Eric Hauenstein · Accepted Answer · 2013-10-03 19:53:12Z

3

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)

edited Oct 3, 2013 at 19:53

answered Oct 3, 2013 at 19:44

Eric Hauenstein

2,5656 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1978340 Over a year ago

Thanks I was way over thinking this

Anup Shah · Accepted Answer · 2013-10-03 19:48:19Z

1

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

answered Oct 3, 2013 at 19:48

Anup Shah

1,25410 silver badges15 bronze badges

1 Comment

user1978340 Over a year ago

Yes I was only thinking of using join I completely forgot the purpose of Union which was why my queries were not working

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related