I have the following tables:
TEAMS:
ID Name
------ ---------
1 Giants
2 Yankees
3 Cool Guys
PLAYERS:
ID Name Team IQ SomeOtherDetail
------ --------- ------- ------ ----------------
1 Bob 1 100 Oklahoma
2 Joe 1 80 Who knows?
3 Sue 2 130 Who cares?
4 Fred 2 76 42
5 Ed 2 90 Yes, please.
6 Schultz 3 314 :-)
My code contains the flowing class:
public class Team
{
public int Id { get; set; }
public string Name { get; set; }
public List<Player> Players { get; set; }
}
Using good old stringy querying and a DataReader, I'm trying to get a list of all the teams, players included.
Is there a way to do this with a single query?
The solutions for this question gets pretty close;
I was considering getting list of players as single string and then splitting them, but it doesn't help because I need all the the players' details (names, IDs, etc.), and besides, it feels like a dirty trick.
Another thing I considered is querying like so:
select *
from TEAMS join PLAYERS
on TEAMS.ID = PLAYERS.Team
...(thus getting extra rows) and then concentrating results using linq, but I'm not sure how efficient this is.
So, any bright ideas?
select t.ID as TeamID, t.Name as TeamName, p.ID as PlayerID, p.Name as PlayerName
, p.Team as TeamName, p.IQ, p.SomeOtherDetail
from Team t
inner join Players p on t.ID = p.Team
That would give you the basic SQL. Then you'd just need to loop through the results, check if the team already exists in your list and add it if not, and then add the player to the list of players.
Does that answer your question?