There is no case where there are multiple Leader/Assistant in the TEAMS-Table. Both of these only occur once per project.
There are multiple ways you could do this, including those suggested by the question title, but I find a conditional
MAX() the simplest to grok (and most likely to only ever access any table once). You want one row per
nameproject, and then you need to find the team row that has the specific task associated with it. You could just as easily use
MIN() instead of
MAX(), if you trust your data it will be the same, if you don't trust your data that will just determine how to break ties.
SELECT p.idproject, p.nameproject, leader = MAX(CASE t.taskTeam WHEN 'leader' THEN t.nameTeam END), assistant = MAX(CASE t.taskTeam WHEN 'assistant' THEN t.nameTeam END) FROM dbo.PROJECT AS p INNER JOIN dbo.TEAMS AS t -- plural but project is not? ON p.idproject = t.idproject WHERE t.taskTeam IN ('leader', 'assistant') GROUP BY p.idproject, p.nameproject ORDER BY p.idproject;
Note: I assumed
assistent was a typo, and that
I'd do this with cte's (common table expressions). By breaking up the Teams table based on the taskteam column, then joining them together on the idproject column:
with leaders as (select idproject, nameTeam from teams where taksteam = 'leader'), assistants as (select idproject, nameTeam from teams where taksteam = 'assistant'), poor_guys as (select idproject, nameTeam from teams where taksteam = 'poor guy') select p.*, l.name, a.name, pg.name from project p left join leaders l on p.idproject = l.idproject left join assistants a on p.idproject = l.idproject left join poor_guys pg on p.idproject = pg.idproject order by p.name_project
If you've got more than three people in a team, you are going to run into additional complications though - you'll want to use the rank() function to further seperate out your teams' roles.