It's that time once again when I ask for help for something I really know I should be able to do myself, but it's making my brain hurt.
Imagine a table of contacts/people. For our purposes, it need only consist of a unique id and a name.
Next is a table of relationship types (contact owner, line manager, etc) in which the relationship with the id of 5 is contact owner.
Finally there is a table of actual contact relationships, in which each row, for the purposes of this exercise consists of two contact ids and a relationship id. So if the contact with id 387 is owned by the person with id 12, the row would be "387, 12, 5".
What I now want is a list of contacts that exist in the first table, but are not listed in the third table in any row that has 5 in the last column. That is, a list of extant contacts with no owner.
This feels like it should be easy, but either it's not, or I'm missing something obvious. I think the main problem is that I'm trying to search for a negative.
Any pointers as to where to start would be gratefully received. A complete solution in SQL (MSSQL if it makes any difference) would receive a "wow, thanks", or something along those lines.
Also, a person can have any given type of relationship with exactly one other person. He can be owned by one, and managed by another. He may have up to seven relationships with up to seven people, or he may have no relationships at all.
Imagine a table of contacts/people. For our purposes, it need only consist of a unique id and a name.
Next is a table of relationship types (contact owner, line manager, etc) in which the relationship with the id of 5 is contact owner.
Finally there is a table of actual contact relationships, in which each row, for the purposes of this exercise consists of two contact ids and a relationship id. So if the contact with id 387 is owned by the person with id 12, the row would be "387, 12, 5".
What I now want is a list of contacts that exist in the first table, but are not listed in the third table in any row that has 5 in the last column. That is, a list of extant contacts with no owner.
This feels like it should be easy, but either it's not, or I'm missing something obvious. I think the main problem is that I'm trying to search for a negative.
Any pointers as to where to start would be gratefully received. A complete solution in SQL (MSSQL if it makes any difference) would receive a "wow, thanks", or something along those lines.
Also, a person can have any given type of relationship with exactly one other person. He can be owned by one, and managed by another. He may have up to seven relationships with up to seven people, or he may have no relationships at all.
via International Skeptics Forum http://ift.tt/1Nh2yQu
Aucun commentaire:
Enregistrer un commentaire