#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    2
    Rep Power
    0

    Please help with a query!


    These are my tables:

    pid | name
    1 A
    2 B
    3 C
    4 D
    5 E

    tid | pid 1 | pid 2 | pid 3
    1 1 2 3

    Bascially, I would like to write a query to list only the names which their "pid" match those pids in the other table. If anyone knows, pls help!!

    Many Thanks!!

    Joe
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Hey Joe,

    First off are you sure you want to set your tables up like that?

    Wouldn't it be better to have

    pid_table:
    pid | name

    tid_table:
    tid | pid

    This way you won't be limited to only 3 pids for each tid.

    Then you can just do a SELECT tid, name FROM pid_table, tid_table WHERE pid_table.pid = tid_table.pid .

    If you want to limit the max number of matches for pids to tids you can use a plpgsql function to enforce this.

    HTH
    -b


    **EDIT**
    Hmm, looking at your question again, I'm not sure if I addressed it correctly, if not please let me know and just restate what you're asking so I can understand it better.
    Last edited by bcyde; July 14th, 2003 at 03:44 PM.
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    2
    Rep Power
    0
    Hi,

    Sorry if I confused you. 'pid' stand for playerID and 'tid' stand for teamID and so the set up of those tables are necessary. Each team has a number of players selected from the player table.
    Anyway I have solved the problem but thanks for your suggestion.

    Regards,

    Joe
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Canada
    Posts
    4
    Rep Power
    0

    bcyde is correct


    You still should set up your tables differently, if only to get into the habit of doing so.
    I recently built a very large, very complex system for team/player management.

    The way I did it was:

    players p

    teams t

    playerteams pt

    where pt.player_id = p.id and pt.team_id = t.id

    This gives you the ability to have mutliple players on multiple teams and much less redundancy or repetition of data entry.

    Just a thought

IMN logo majestic logo threadwatch logo seochat tools logo