CODEWITHSUNDEEP
sqldatabasepostgresql
Del boy
Del boy

Reputation: 107

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" ?

Upvotes: 0

Views: 3694

Answers (3)

Hart CO
Hart CO

Reputation: 34774

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.time END) AS  goal
             , MAX(CASE WHEN pma.activity_id = '2' THEN pma.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.

Upvotes: 1

Bill
Bill

Reputation: 125

What you're trying to do is basically called a PIVOT. T-SQL has a PIVOT keyword, but I'm not sure if you're using T-SQL, so here's a non T-SQL version:

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

I took out the matches table because you don't appear to be using anything from it. But I also assumed that team_match has a match_id field in it to make up for it. If you want to return more statistics from the query, the you can add new entries to both SELECT and the pm.activity_id IN ('1', '2').

Upvotes: 1

David Bachmann Jeppesen
David Bachmann Jeppesen

Reputation: 395

The way this query is created, the easiest way to do it is to join the player_match_activity table twice:

SELECT DISTINCT p.name, pma_goal.time AS goal, pma_assist.time AS assist
FROM player p JOIN player_match pm ON p.player_id = pm.player_id
JOIN matches m ON m.match_id = pm.match_id
JOIN team_match tm ON tm.team_id = p.team_id
LEFT 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
LEFT 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';

This is probably not the best way to do this SELECT, but it is my minimal change suggestion. ;)

Upvotes: 1

Related Questions