query-problem 1

[stwi1974]

I got the following query

select t.id, pers1.name name1, pers1.firstname firstname1,
pers2.name name2, pers2.firstname firstname2,
t.discipline, t.number
from team t
join team_person tpers1 on tpers1.team_id=t.id
join person pers1 on pers1.id=tpers1.person_id
left join team_person tpers2 on tpers2.team_id=t.id and
tpers2.person_id not in (select tpers21.person_id
from team_person tpers21
where tpers21.person_id=tpers1.person_id)
left join person pers2 on pers2.id=tpers2.person_id

resultuing with:
id name1 firstname1 name2 firstname2 discipline number
1 Miller Steven NULL NULL Single 1
2 Miller Steven Smith Susan Mixed 2
2 Smith Susan Miller Steven Mixed 2

but the mixed-combination Miller/Smith shouldnt result twice. How should i change the query to get the following result?
id name1 firstname1 name2 firstname2 discipline number
1 Miller Steven NULL NULL Single 1
2 Miller Steven Smith Susan Mixed 2

Thanks in advance.

 

[manjarikosaraju]

TRY THIS...The column names and table names in the below query might not match exactly with yours...

select A.TeamId, A.Name1, A.FirstName1, A.Name2, A.FirstName2, A.Discipline, A.number
from
(
select tp1.teamId, p.PersonId, p.LName as Name1 , p.FName as FirstName1,
p2.LName as Name2, p2.FName as FirstName2, t.discipline, t.number
from #Team t join #TeamPerson tp1 on t.Id = tp1.teamid
join #Person p on tp1.personId = p.personId
left join #TeamPerson tp2 on t.Id = tp2.teamId and tp1.personId <> tp2.personId
left join #Person P2 on p2.personId = tp2.personId
) A
JOIN (select teamId, min(PersonId) as PID from #TeamPerson group by teamId) B
on A.TeamId = B.TeamId
WHERE A.PersonId = B.PID

Hope this helps.

 

[stwi1974]

Yes, thanks alot!