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

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.

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

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