Del boy Del boy - 6 months ago 15
SQL Question

Retrieve same column twice with different conditions

This question is already answered multiple times but I just can't get it to works. I tied use some answer from this question but I always "get error more than one row returned by a subquery used as an expression"

I have following sql query:

SELECT DISTINCT p.name, pma.time AS goal, pma.time AS assist

FROM player p

INNER JOIN player_match pm
ON p.player_id = pm.player_id

INNER JOIN matches m
ON m.match_id = pm.match_id

INNER JOIN team_match tm
ON tm.team_id = p.team_id

FULL JOIN player_match_activity pma
ON pma.player_id = p.player_id
AND pma.activity_id = '1'
AND pma.match_id = m.match_id

WHERE m.match_id = '163'
AND tm.home_away = 'home'


The query gives me following result:

name | goal | assist
-------------------------------------
Ronaldo 1 1
Messi 3 3
Vardy


The column "assist" show same values like the column "goal".
Line pma.activity_id = '1' select just goals.

How can I set that the column "assist" use exact same conditions like the column "goal" BUT instead of pma.activity_id = '1' I want to change it to '2" ?

Answer

You could add another join to the player_match_activity table, or you could change pma.activity_id = '1' to pma.activity_id IN ('1','2') and use CASE expressions to choose the populate the proper columns:

SELECT DISTINCT p.name, pma_goal.time AS  goal, pma_assist.time AS assist 
FROM player p
INNER JOIN player_match pm
   ON p.player_id = pm.player_id
INNER JOIN matches m
   ON m.match_id = pm.match_id
INNER JOIN team_match tm
   ON tm.team_id = p.team_id 
FULL JOIN player_match_activity pma_goal
   ON pma_goal.player_id = p.player_id
   AND pma_goal.activity_id = '1'
   AND pma_goal.match_id = m.match_id    
FULL JOIN player_match_activity pma_assist
   ON pma_assist.player_id = p.player_id
   AND pma_assist.activity_id = '2'
   AND pma_assist.match_id = m.match_id    
WHERE m.match_id = '163'
  AND tm.home_away = 'home'

Alternatively:

SELECT p.name, MAX(CASE WHEN pma.activity_id = '1' THEN pma_goal.time END) AS  goal
             , MAX(CASE WHEN pma.activity_id = '2' THEN pma_goal.time END) AS  assist
FROM player p
INNER JOIN player_match pm
   ON p.player_id = pm.player_id
INNER JOIN matches m
   ON m.match_id = pm.match_id
INNER JOIN team_match tm
   ON tm.team_id = p.team_id 
FULL JOIN player_match_activity pma
   ON pma.player_id = p.player_id
   AND pma.activity_id IN ('1','2')
   AND pma.match_id = m.match_id    
WHERE m.match_id = '163'
  AND tm.home_away = 'home'
GROUP BY p.name

Also, not sure you need to be using FULL JOIN here.

Comments