And this would be one way to express the query that you're looking for.
Feel free to add age to schema and query.
SELECT PlayerName
, (SELECT COUNT(*) FROM Team WHERE TeamId = Player.TeamId AND TeamLevel =
'A') As LevelA
, (SELECT COUNT(*) FROM Team WHERE TeamId = Player.TeamId AND TeamLevel =
'B') As LevelB
, (SELECT COUNT(*) FROM Team WHERE TeamId = Player.TeamId AND TeamLevel =
'C') As LevelC
FROM Player
"Larry S" <nospam@bugus.zzz> wrote in message
news:M17Yc.107934$TI1.103116@attbi_s52...[color=blue]
> In SQL it might be something like this to start with. Start reading about
> normalization, relational theory, and table driven applications.
>
> CREATE TABLE Team
> ( TeamId INTEGER NOT NULL PRIMARY KEY
> , TeamName VARCHAR(40) NOT NULL
> , TeamLevel CHAR(1) NOT NULL CHECK (TeamLevel IN ('A', 'B', 'C'))
> )
>
> CREATE TABLE Player
> ( PlayerId INTEGER NOT NULL PRIMARY KEY
> , PlayerName VARCHAR(40) NOT NULL
> , TeamId INTEGER NOT NULL FOREIGN KEY REFERENCES Team(TeamId)
> )
>
> "Alan Zhong" <alanchinese@yahoo.com> wrote in message
> news:b09c98a8.0408260048.79651f10@posting.google.c om...[color=green]
>>i am a beginner of database design, could anyone please help me to
>> figure out how to make these two tables work.
>>
>> 1) a "players" table, with columns "name", "age"
>> 2) a "teams" table, which can have one OR two player(s)
>> a team also has a column "level", which may have values "A", "B",
>> or "C"
>>
>> how do you build the "teams" table (the critical question is "do i
>> need to create two fields" for the maximum two possible players?")
>>
>> how do you use one query to display the information with the following
>> columns:
>> "name", "age", "levelA", "levelB", "levelC" (the later three columns
>> are integer type, showing how many teams with coresponding level this
>> player is in).
>>
>> now suppose i don't have any access to sql server, i save the data
>> into xml, and load it into a dataset. how could you do the selection
>> within the dataset? or ahead of that, how do you specify the relations
>> between "players" and "teams".
>>
>>
>> the following is the schema file i am trying to make (I still don't
>> know if i need to specified the primary key... and how to build
>> relation between them):
>> <code>
>> <?xml version="1.0" ?>
>> <xs:schema id="AllTables" xmlns=""
>> xmlns:xs="http://www.w3.org/2001/XMLSchema"
>> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
>> <xs:element name="AllTables" msdata:IsDataSet="true">
>> <xs:complexType>
>> <xs:choice maxOccurs="unbounded">
>> <xs:element name="Players">
>> <xs:complexType>
>> <xs:sequence>
>> <xs:element name="PlayerID" msdata:AutoIncrement="true"
>> type="xs:int" minOccurs="0" />
>> <xs:element name="Name" type="xs:string" minOccurs="0" />
>> <xs:element name="Age" type="xs:int" minOccurs="0" />
>> </xs:sequence>
>> </xs:complexType>
>> </xs:element>
>> <xs:element name="Teams">
>> <xs:complexType>
>> <xs:sequence>
>> <xs:element name="TeamID" msdata:AutoIncrement="true"
>> type="xs:int" minOccurs="0" />
>> <xs:element name="Level" type="xs:string" minOccurs="0" />
>> <xs:element name="Player1" type="xs:int" minOccurs="0" />
>> <xs:element name="Player2" type="xs:int" minOccurs="0" />
>> </xs:sequence>
>> </xs:complexType>
>> </xs:element>
>> </xs:choice>
>> </xs:complexType>
>> </xs:element>
>> </xs:schema>
>> </code>[/color]
>
>[/color]
