CODEWITHSUNDEEP
sqlsqlite
delca7
delca7

Reputation: 97

SQL - Selecting a column from another table twice with different values

I have a database with two tables: one is a table for people, indicating which sports they practice; the second is a sports table shows which sports represent each id.

persons table

id   name  sport1  sport2
100  John  0       3
101  Max   1       3
102  Axel  2       4
103  Simon 4       2

sports table

sportid  sportn
0        Football
1        Baseball
2        Basketball
3        Hockey
4        Swimming

I want to do a query where it shows me what sports Max practices, something like this

id   name   sport1      sport2
101  Max    Baseball    Hockey

So far I got this

select p.id, p.name, s.sportn, s.sportn 
  from persons as p, sports as s
 where p.sport1 = s.sportid and p.id = 101

This shows me the first sport twice, so I don't know where to go from here.

Upvotes: 0

Views: 1813

Answers (3)

Simon
Simon

Reputation: 1605

The problem with your query is that you are only joining on the sports table once with p.sport1.

This query should give you what you need : 

SELECT p.id, p.name, s1.sportn AS sport1, s2.sportn AS sport2
FROM persons AS p
    JOIN sports AS s1 ON p.sport1 = s1.sportid
    JOIN sports AS s2 ON p.sport2 = s2.sportid
WHERE p.id = 101

You could also read on sql joins to help you understand this solution better.

Finally, if you really need to use your current syntax, here is the corrected query :

SELECT p.id, p.name, s1.sportn AS sport1, s2.sportn AS sport2 
FROM persons AS p, sports AS s1, sports AS s2
WHERE p.sport1 = s1.sportid AND p.sport2 = s2.sportid AND p.id = 101

Upvotes: 3

ErstwhileIII
ErstwhileIII

Reputation: 4843

You might try the following (since each sport column in people requires a different join).

select p.id as ID, p.name as Name, s1.name as Sport1, s2.name as Sport2
  from persons as p, sports as s1, sports as s2
 where p.sport1 = s1.sportid
   and p.sport2 = s2.sportid
   and p.id = 101

Upvotes: 0

Lamak
Lamak

Reputation: 70638

This should do:

SELECT  p.id, 
        p.name, 
        s1.sportn AS sport1, 
        s2.sportn AS sport2
FROM persons AS p
LEFT JOIN sports AS s1
    ON p.sport1 = s1.sportid 
LEFT JOIN sports AS s2
    ON p.sport2 = s2.sportid 
WHERE p.id = 101

Upvotes: 0

Related Questions