I have a database whose layout is out of my control, which contains two entities, "Person" and "Trait". Each person may have multiple traits and each trait may be associated to multiple persons. There's an intermediate entity for this relationship:
So far so good. I want to create a list with all the traits, the persons who do have a given trait, and the persons who do not have that same trait. The positives are simple:
SELECT t.id,
t.name,
positives.count,
positives.members
FROM Trait t
JOIN (
SELECT t.id,
count() AS count,
group_concat(p.name) AS members
FROM Person p
JOIN Person_Trait pt ON pt.id_person = p.Id
JOIN Trait t ON t.id = p.id_trait
GROUP BY t.id
)
AS positives ON t.id = positives.id;
However I'm stumped on how to get the negative group such that I can join against it. Any ideas?
I can make views, if those might help.
Aucun commentaire:
Enregistrer un commentaire