Selecting two values from a single column and making a result row

Question

My problem seems simple but I just can't figure it out. I have a single table that I need to construct a two column result from, using the values out of just one column in that table...

ID - ENTITY - CATEGORY
1    Bread    Food
2    Apple    Food
3    Computer Electronics
4    Chair    Furniture
5    TV       Electronics
6    Sofa     Furniture

The results I need should look like this...

EAT_THIS - SIT_HERE
Bread      Chair

Example (wrong) code...

select ENTITY as EAT_THIS from table where CATEGORY = 'Food'
select ENTITY as SIT_HERE from table where CATEGORY = 'Furniture'

.... but I can't seem to figure out how to get these two results on the same line like my example results show. Not sure if this makes sense, please let me know if further detail is needed.

Thanks

Why `Bread - Chair` and not `Bread - Sofa`? What are the rules you want to apply? — peterm, Dec 10 '13 at 23:01

score 0 · Answer 1 · edited May 23 '17 at 10:33

Using a join you can achieve this:

SELECT A.entity as EAT_THIS, B.entity as SIT_HERE
FROM tablename A
JOIN tablename B
WHERE A.category = 'Food'
AND B.category = 'Furniture'

However, there is no guarantee that your results will be unique. If anything you'll end up with this:

Bread, Chair
Apple, Chair

...instead of (what I assume you want):

Bread, Chair
Apple, Sofa

Cross joins will help you in this case, see this article: https://stackoverflow.com/a/16471286/2812842

More info

score 0 · Answer 2 · answered Dec 10 '13 at 22:02

If you don't specify a relation between the two tables (or in our case the same table), All the possibilities from the Cartesian product will show up

SELECT A.ENTITY AS EAT_THIS , B.ENTITY AS SIT_HERE
FROM tablename A,tablename B
WHERE A.category = 'Food'
AND B.category = 'Furniture'

Selecting two values from a single column and making a result row

2 Answers2