Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Unanswered: Basic Query problem

    Hello pals,

    i am new to databases, and i am sort of struggling here to make this query work...

    So i have 3 tables.

    HTML Code:
    create table Player (
    Player_id varchar (8) Primary Key ,Name varchar (25),Surname varchar (35),
    birth_date date     )
    HTML Code:
    create table Game ( 
    Game_id varchar (8) Primary Key ,Place varchar (90),Player_TA varchar (35),
    Player_TB varchar (35),Game date    )

    HTML Code:
    create table Participation   (
    P_id integer primary key NOT NULL identity(1,1),Player_id varchar(8),
    Game_id varchar (8)    )
    Paticipation contains only keys so i have filled it like this:

    HTML Code:
    insert into Participation (Player_id,Game_id) values ('P01','G01')
    insert into Participation (Player_id,Game_id) values ('P01','G02')
    insert into Participation (Player_id,Game_id) values ('P02','G01')
    
    etc 
    etc
    So what iam i trying to do ? I have to show the Player , with the most participations in games (Player_TA/Player_TB)

    This is my approach:

    HTML Code:
    select  Name,Surname, COUNT(Game_id) AS 'Number of Particip'
    
    From Participation,Player
    
    Where Player.Player_id=Participation.Player_id
    
    group by Name,Surname order by COUNT(Game_id) desc
    And this returns a list with the Players with the most participations to games , where top player of the list is the one with most participations.

    Example:

    HTML Code:
    Name     Surname     Number of Particip
    
    abc        def                15
    
    hij        klm                13
    
    ops        tyi                9
    
    etc...

    My target is :

    HTML Code:
    Name     Surname     Number of Particip
    
    abc        def                15
    What am i supposed to do ?

    I just cant think right now.... >.>

    Thankieees

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add TOP 1 right after the keyword SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    8
    Quote Originally Posted by r937 View Post
    add TOP 1 right after the keyword SELECT
    Oh my... i feel soo... Anyway , thanks ALOT pal.

    Other than that , how does my db look? Is it ok? I mean can you spot some design fails that i cant see >.< ?

    Cheers

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Db_Addicted View Post
    Other than that , how does my db look?
    what are player_ta and player_tb for?


    your participation table should not have an identity column
    Code:
    CREATE TABLE participation
    ( player_id VARCHAR(8) NOT NULL
    , game_id   VARCHAR(8) NOT NULL
    , PRIMARY KEY ( player_id , game_id )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2012
    Posts
    8
    Quote Originally Posted by r937 View Post
    what are player_ta and player_tb for?
    Hey, thanks

    player_ta and player_tb are tennis players. let me show you how i filled the tables



    HTML Code:
    insert into Player values('P01','John','Green','10/07/1985')
    insert into Player values('P02','Thomas','Jones','12/10/1983')
    
    insert into Game values('G01','Paris','John Green','Thomas Jones','10/14/2011')
    
    insert into Game values('G02','London','John Green','Peter Young','11/10/2010')
    Does this make any sense? :-)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Db_Addicted View Post
    Does this make any sense? :-)
    it might, if you didn't also have a "participation" table... what's that for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2012
    Posts
    8
    Quote Originally Posted by r937 View Post
    it might, if you didn't also have a "participation" table... what's that for?
    its meant to be "the participation of a player into a game" thats why it stores and links a game to a player.

    I mean without participation, we couldnt link a player to the game, those two tables have nothing in common, except the names, but the names are stored in different formats if you see, so they cant be linked.

    Or am i totally wrong? Its my fist attempt on db'ses so bare with my ignorance.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Db_Addicted View Post
    I mean without participation, we couldnt link a player to the game...
    but you already did, by having player_ta and player_tb in the games table!

    something is redundant here, n'est-ce pas?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2012
    Posts
    8
    Quote Originally Posted by r937 View Post
    but you already did, by having player_ta and player_tb in the games table!

    something is redundant here, n'est-ce pas?

    Hmm, i see... I guess it is.

    Maybe player_ta and player_tb are nicknames... i gotta find out!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •