Seleccionar personas en la tabla y excluir a la esposa, pero combinar sus nombres

Tengo una table Person :

 PersonID | FirstName | LastName ------------------------------- 1 | John | Doe 2 | Jane | Doe 3 | NoSpouse | Morales 4 | Jonathan | Brand 5 | Shiela | Wife 

Y una tabla de Relationship :

 RelationshipID | PersonID | Type | RelatedPersonID 1 | 1 | 3 | 2 2 | 2 | 3 | 1 3 | 4 | 3 | 5 4 | 5 | 3 | 4 

Básicamente, quiero combinar los nombres del cónyuge y el cliente, pero quiero excluir al cónyuge:

Resultados previstos:

 1, John and Jane Doe, 2 ---------------------- 3, NoSpouse Morales, null ----------------------- 4, Jonathan and Shiela Brand, 5 

He intentado:

 SELECT p.PersonID, Case when spouse.PersonID is not null THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName ELSE p.FirstName + ' ' + p.LastName END as ClientName, spouse.PersonID as RelatedPersonID FROM Person p LEFT JOIN Relationship r on p.PersonID = r.PersonID LEFT JOIN Person spouse on r.RelatedPersonID = spouse.PersonID WHERE r.Type = 3 OR spouse.PersonID is null 

pero los resultados son:

 1, John and Jane Doe, 2 ---------------------- 2, Jane and John Doe, 1 ---------------------- 3, NoSpouse Morales, null ----------------------- 4, Jonathan and Shiela Brand, 5 ------------------------------- 5, Shiela and Jonathan Wife, 4 

Aquí hay algunos datos falsos:

 create table Person( PersonID int primary key, FirstName varchar(max), LastName varchar(max) ) insert into Person values (1, 'John', 'Doe'), (2, 'Jane', 'Doe'), (3, 'NoSpouse', 'Morales'), (4, 'Jonathan', 'Brand'), (5,'Shiela','Wife') create table Relationship ( RelationshipID int, PersonID int references Person(PersonID), Type int, RelatedPersonID int references Person(PersonID) ) insert into Relationship values (1, 1, 3, 2), (2, 2, 3, 1), (3, 4, 3, 5), (4, 5, 3, 4) SELECT p.PersonID, Case when spouse.PersonID is not null THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName ELSE p.FirstName + ' ' + p.LastName END as ClientName, spouse.PersonID as RelatedPersonID FROM Person p LEFT JOIN Relationship r on p.PersonID = r.PersonID LEFT JOIN Person spouse on r.RelatedPersonID = spouse.PersonID WHERE r.Type = 3 OR spouse.PersonID is null drop table Relationship drop table Person 

gracias de antemano por su ayuda y time.

NOTA: He editado mi secuencia de commands simulada para include 3, NoSpouse Morales, null en los resultados. Además, no hay criterios particulares necesarios para lo que es marido / mujer. Quien fue el primero en la list no debe include al cónyuge relacionado.

Si uno tiene que ser incluido mientras que el otro está excluido, intente agregar una cláusula

 AND r.PersonID < r.RelatedPersonID 

ya que los ID no serán iguales y esto includeá solo uno:

  SELECT p.PersonID, Case when spouse.PersonID is not null THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName ELSE p.FirstName + ' ' + p.LastName END as ClientName, spouse.PersonID as RelatedPersonID FROM Person p LEFT JOIN Relationship r on p.PersonID = r.PersonID LEFT JOIN Person spouse on r.RelatedPersonID = spouse.PersonID WHERE (r.Type = 3 AND r.PersonID < r.RelatedPersonID) OR spouse.PersonID is null 

Esto podría funcionar

 SELECT p.PersonID, Case when spouse.PersonID is not null THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName ELSE p.FirstName + ' ' + p.LastName END as ClientName, spouse.PersonID as RelatedPersonID FROM Person p LEFT JOIN Relationship r on p.PersonID = r.PersonID and r.Type = 3 LEFT JOIN Person spouse on r.RelatedPersonID = spouse.PersonID WHERE coalesce(r.PersonID, 1) < coalesce(r.RelatedPersonID, 2) 

De esta forma, solo tiene en count las filas de relaciones donde PersonID es menor que RelatedPersonID , o las filas que no se unen a la tabla de Relationships (porque 1 < 2 siempre es verdadero)

Si entiendo correctamente, puede simplemente eliminar a los cónyuges en la cláusula WHERE :

 SELECT PersonId, ClientName, RelatedPersonID FROM (SELECT p.PersonID, (CASE WHEN spouse.PersonID is not null THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName ELSE p.FirstName + ' ' + p.LastName END) as ClientName, spouse.PersonID as RelatedPersonID, spouse.LastName as spouse_lastname FROM Person p LEFT JOIN Relationship r ON p.PersonID = r.PersonID LEFT JOIN Person spouse ON r.RelatedPersonID = spouse.PersonID WHERE r.Type = 3 ) prs WHERE spouse_LastName <> 'Wife'; 

Prueba esto

 create table Person( PersonID int primary key, FirstName varchar(max), LastName varchar(max) ) insert into Person values (1, 'John', 'Doe'), (2, 'Jane', 'Doe'), (3, 'NoSpouse', 'Morales'), (4, 'Jonathan', 'Brand'), (5,'Shiela','Wife') create table Relationship ( RelationshipID int, PersonID int references Person(PersonID), Type int, RelatedPersonID int references Person(PersonID) ) insert into Relationship values (1, 1, 3, 2), (2, 2, 3, 1), (3, 4, 3, 5), (4, 5, 3, 4) SELECT p.PersonID, Case when spouse.PersonID is not null THEN p.FirstName + ' and ' + spouse.FirstName + ' ' + p.LastName ELSE p.FirstName + ' ' + p.LastName END as ClientName, spouse.PersonID as RelatedPersonID FROM Person p LEFT JOIN Relationship r on p.PersonID = r.PersonID AND r.Type = 3 LEFT JOIN Person spouse on r.RelatedPersonID = spouse.PersonID WHERE r.RelationshipID IS NULL OR r.RelationshipID < r.RelatedPersonID drop table Relationship drop table Person 

Enlace demo: Rextester