I'm quite new to databases, and I figured that it would be fun to setup a a Survivor database website for me and my friends.
So far I have...
Survivors
id
name | string
tribe | string
gone | boolean
placegone | int
picks
id
name | string
pick1 | int
pick2 | int
...
pick16 | int
basically picks.name is a friends name and picks.pick1 - picks.pick16 are their choices for who will get voted off first - last.
I've managed to use this select statement to pick out each person, and their first pick.
SELECT [projects].[name], [workers].[name]
FROM workers INNER JOIN projects ON [workers].[id]=[projects].[pick1];
that works fine because I could also change the last part to pick# (1<= # <= 16) and pull out each choice.
The problem is that I want to create a website with a table and all that good stuff, so do I need to have 16 different SQL statements (one for each pick) to fill in the table, or is there a more elegant way of doing this?
Ideally it'd be a big loop which create the table and populate it with the data from the database.
do I need to redesign the database? or is it just my SQL statements that need tweaking?
thanks a lot for the help
-Mike.
So far I have...
Survivors
id
name | string
tribe | string
gone | boolean
placegone | int
picks
id
name | string
pick1 | int
pick2 | int
...
pick16 | int
basically picks.name is a friends name and picks.pick1 - picks.pick16 are their choices for who will get voted off first - last.
I've managed to use this select statement to pick out each person, and their first pick.
SELECT [projects].[name], [workers].[name]
FROM workers INNER JOIN projects ON [workers].[id]=[projects].[pick1];
that works fine because I could also change the last part to pick# (1<= # <= 16) and pull out each choice.
The problem is that I want to create a website with a table and all that good stuff, so do I need to have 16 different SQL statements (one for each pick) to fill in the table, or is there a more elegant way of doing this?
Ideally it'd be a big loop which create the table and populate it with the data from the database.
do I need to redesign the database? or is it just my SQL statements that need tweaking?
thanks a lot for the help
-Mike.