I have two table in my database. One called person and one called team. A person can be on one team. How do I check to find all the people in person that are not on a team using LINQ or sql??
Asked
Active
Viewed 92 times
-4
-
only things that showed what was there – Oedum Nov 21 '11 at 13:28
-
4Make a proper question, give example table data, example result. – Utku Yıldırım Nov 21 '11 at 13:28
-
you need to be more specific, if we ar enot allowed to use LINQ or SQL what shall we use ? – Richard Nov 21 '11 at 13:29
-
1@Richard: He seems to be asking for a solution using *either*. I think either one is allowed. – Adam Robinson Nov 21 '11 at 13:30
-
you are alowed to use linq or SQL... it's just how to find the people who are not on a team – Oedum Nov 21 '11 at 13:32
3 Answers
3
A person can be on one team
This makes me guess that you have a TeamID FK field in the person's table that can have null values.
How do I check to find all the people in person that are not on a team
This will give you all persons not in a team.
select *
from person
where TeamID is null
Mikael Eriksson
- 136,425
- 22
- 210
- 281
0
You mean something like this?
select *
from person p
join team t
on p.TeamId = t.TeamId
where p.TeamId <> 3
or in linq:
from p in persons
join t in teams on p.TeamId equals t.TeamId
where t.TeamId != 3
select p
both queries will return all persons that are not on the team with ID 3.
Johnie Karr
- 2,744
- 2
- 35
- 44
-
this is a generic solution, but if the community knew your table definitions then we could be more specific. – Johnie Karr Nov 21 '11 at 13:32
-
He's looking for someone not on a team at all, not people not on a *particular* team, it seems. – Adam Robinson Nov 21 '11 at 13:42
-
-
-
0
How do you represent the linkage of a person to a team. Do you use a teamID field in person? If so, what do you do when a person is not on a team? Keep it null?
select * from person where teamID is null
Set it to -1?
select * from person where teamID == -1
Maybe you have a person_team cross table (which would be odd because you said that a person could only be on one team). If so
SELECT * FROM person LEFT OUTER JOIN person_team
ON (person.id = person_team.personID)
WHERE person_team.personID IS NULL
Or if you have something else -- tell us.
Lou Franco
- 87,846
- 14
- 132
- 192
-
I would upvote this if it didn't include the "magic number" approach. – Adam Robinson Nov 21 '11 at 13:41
-
I'm not suggesting it -- but, in case he's using it, that's the way to select. – Lou Franco Nov 21 '11 at 14:34