Creator13 Creator13 - 1 year ago 59
SQL Question

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

I have two tables:

stores all the received votes (each person can give a first and second vote),
holds the items people can vote for. Each item has its own ID. The
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:


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


**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 twice. In the result of Mark, this is Jane and Mary.

vkp vkp
Answer Source

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

select v.voter,, 
from votes v 
join submissions s1 on v.vote1_id =
join submissions s2 on v.vote2_id =
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download