Creator13 Creator13 - 2 months ago 7
SQL Question

SQL select two rows from same table (same column, in fact), based on join with other table

I have two tables:

votes
and
submissions
.
votes
stores all the received votes (each person can give a first and second vote),
submissions
holds the items people can vote for. Each item has its own ID. The
votes
table stores the ID of each vote. I want to retrieve the names of the items people votes on. Here's an example of what the tables look like:

votes:


**voter** | **vote1_ID** | **vote2_ID**
Foo | 1 | 2
Bar | 3 | 2
Mark | 2 | 3


submissions:


**ID** | **name**
1 | John
2 | Jane
3 | Mary


I already stated I want to retrieve both the name associated with the first vote and the name associated with the second vote within one query (in fact, I don't really care how many queries it takes, but a single query is always nicer and cleaner of course). How would I go on doing this? I already tried figured I need to use a join, but I can't figure out how to retrieve the value from a same column twice.

EDIT: I figured giving an example of what query I'm trying to perform might be useful:

For example, if I want to see what Bar has voted for, the result of the query should be submissions.name twice. In the result of Mark, this is Jane and Mary.

vkp vkp
Answer

You have to join the submissions table twice to get the expected result.

select v.voter, s1.name, s2.name 
from votes v 
join submissions s1 on v.vote1_id = s1.id
join submissions s2 on v.vote2_id = s2.id