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

Question

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.

Simon · Accepted Answer · 2014-08-08T19:18:30.027

3

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

edited Aug 08 '14 at 19:18

answered Aug 08 '14 at 19:10

Simon

1,605
13
22

There were other replys wich also worked, one of them wasn't using joins but had the same result, is there any difference overall? – delca7 Aug 08 '14 at 19:26
@delca7 Using joins is the right way to do it. With bigger queries the old syntax will become very hard to understand (you will have difficulties differencing the real WHERE clause with the actual joins between the tables) – Simon Aug 08 '14 at 19:28
@delca7 check http://stackoverflow.com/questions/11251751/which-join-syntax-is-better – Simon Aug 08 '14 at 19:29
let's say I have a simple database which won't be using a lot of querys, is it acceptable i I don't use it in that specific case? – delca7 Aug 08 '14 at 19:30
@delca7 You should clearly learn how to join between tables properly using the JOIN clause. It could be "acceptable" but I won't recommend you to do it because of what I said and what is in the post I linked. – Simon Aug 08 '14 at 19:31
Query: Is it more effective to do the sports_id check in the where clause than in the on clause? And one might argue for the use of LEFT JOIN if a sport has become history. – Erik Oct 03 '17 at 05:54

score 0 · Answer 2 · answered Aug 08 '14 at 19:10

0

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

answered Aug 08 '14 at 19:10

Lamak

69,480
12
108
116

score 0 · Answer 3 · answered Aug 08 '14 at 19:11

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

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

3 Answers3