CODEWITHSUNDEEP
sqljoin
cvbattum
cvbattum

Reputation: 799

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.

Upvotes: 0

Views: 2673

Answers (3)

Brian Pendleton
Brian Pendleton

Reputation: 829

And if you want rows instead of columns, you could do two joins and union them together:

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

Upvotes: 0

Eric G
Eric G

Reputation: 2617

You can do two inner joins to select the separate values.

SELECT s1.name, s2.name 
FROM votes v
INNER JOIN submissions s1 ON v.vote1_ID = s1.ID
INNER JOIN submissions s2 ON v.vote2_ID = s2.ID

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

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

Upvotes: 1

Related Questions