SQL query for a game's database

SQL query for a game's database

Post by Amado » Wed, 14 May 2003 14:54:56



I have a database related to a table game. The structure
is as follows:

Table1 contains the names of all players and arbiters, it
has only two fields, an identity field with an integer
value that uniquely identifies each name.

Table2 contains data about every game that is played, so
among other simple columns, there are these 3 playerA,
playerB and arbiter that are all lookup to the same
players base (but different rows). To clarify thing here
is one example:

Table1:
0001 Peter
0002 John
0003 Mark
....

Sample row from Table2:
0001 0002 0003 (representing a game Peter played against
John, Mark was the arbiter)

My question is: How do I write a SQL statement to show
data (real names) from all rows on Table 2? Internally
Microsoft Access handles very well the situation, so a
simple query as Select * from Table 2 yields:
Peter, John, Mark
but such query on the webs gets of course
0001, 0002, 0003

I appreciate your answer.

 
 
 

SQL query for a game's database

Post by Jacco Schalkwij » Wed, 14 May 2003 15:27:47


CREATE TABLE table1 (person_id int, name varchar(30))
CREATE TABLE table2 (player_1_id int, player_2_id int, arbiter_id int)

SELECT t1_1.name, t1_2.name, t1_3.name
FROM table2 t2
INNER JOIN table1 t1_1
ON t2.player_1_id = t1_1.person_id
INNER JOIN table1 t1_2
ON t2.player_2_id = t1_2.person_id
INNER JOIN table1 t1_3
ON t2.arbiter_id = t1_3.person_id

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:> I have a database related to a table game. The structure
> is as follows:

> Table1 contains the names of all players and arbiters, it
> has only two fields, an identity field with an integer
> value that uniquely identifies each name.

> Table2 contains data about every game that is played, so
> among other simple columns, there are these 3 playerA,
> playerB and arbiter that are all lookup to the same
> players base (but different rows). To clarify thing here
> is one example:

> Table1:
> 0001 Peter
> 0002 John
> 0003 Mark
> ....

> Sample row from Table2:
> 0001 0002 0003 (representing a game Peter played against
> John, Mark was the arbiter)

> My question is: How do I write a SQL statement to show
> data (real names) from all rows on Table 2? Internally
> Microsoft Access handles very well the situation, so a
> simple query as Select * from Table 2 yields:
> Peter, John, Mark
> but such query on the webs gets of course
> 0001, 0002, 0003

> I appreciate your answer.


 
 
 

SQL query for a game's database

Post by A Chandra Sekha » Wed, 14 May 2003 16:03:27


Dear Amador,
Please check this out

TABLE1 PlayerID | PlayerName
TABLE2 Player1 | Player2 | Player3

SELECT A.PLAYERNAME, B.PLAYERNAME, C.PLAYERNAME
FROM TABLE2
INNER JOIN TABLE1 A ON A.PLAYERID = PLAYER1
INNER JOIN TABLE1 B ON B.PLAYERID = PLAYER2
INNER JOIN TABLE1 C ON C.PLAYERID = PLAYER3

Hope this solves ur Problem


Quote:> I have a database related to a table game. The structure
> is as follows:

> Table1 contains the names of all players and arbiters, it
> has only two fields, an identity field with an integer
> value that uniquely identifies each name.

> Table2 contains data about every game that is played, so
> among other simple columns, there are these 3 playerA,
> playerB and arbiter that are all lookup to the same
> players base (but different rows). To clarify thing here
> is one example:

> Table1:
> 0001 Peter
> 0002 John
> 0003 Mark
> ....

> Sample row from Table2:
> 0001 0002 0003 (representing a game Peter played against
> John, Mark was the arbiter)

> My question is: How do I write a SQL statement to show
> data (real names) from all rows on Table 2? Internally
> Microsoft Access handles very well the situation, so a
> simple query as Select * from Table 2 yields:
> Peter, John, Mark
> but such query on the webs gets of course
> 0001, 0002, 0003

> I appreciate your answer.

 
 
 

SQL query for a game's database

Post by Amado » Fri, 16 May 2003 10:27:06


Thank you very much Mr Jacco and to Chandra too for your
quick replies.

I have implemented the query you propose, and it works
fine! It has really helped me a lot.

Kind regards,
Amador

>-----Original Message-----
>CREATE TABLE table1 (person_id int, name varchar(30))
>CREATE TABLE table2 (player_1_id int, player_2_id int,
arbiter_id int)

>SELECT t1_1.name, t1_2.name, t1_3.name
>FROM table2 t2
>INNER JOIN table1 t1_1
>ON t2.player_1_id = t1_1.person_id
>INNER JOIN table1 t1_2
>ON t2.player_2_id = t1_2.person_id
>INNER JOIN table1 t1_3
>ON t2.arbiter_id = t1_3.person_id

>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.



>> I have a database related to a table game. The
structure
>> is as follows:

>> Table1 contains the names of all players and arbiters,
it
>> has only two fields, an identity field with an integer
>> value that uniquely identifies each name.

>> Table2 contains data about every game that is played,
so
>> among other simple columns, there are these 3 playerA,
>> playerB and arbiter that are all lookup to the same
>> players base (but different rows). To clarify thing
here
>> is one example:

>> Table1:
>> 0001 Peter
>> 0002 John
>> 0003 Mark
>> ....

>> Sample row from Table2:
>> 0001 0002 0003 (representing a game Peter played
against
>> John, Mark was the arbiter)

>> My question is: How do I write a SQL statement to show
>> data (real names) from all rows on Table 2? Internally
>> Microsoft Access handles very well the situation, so a
>> simple query as Select * from Table 2 yields:
>> Peter, John, Mark
>> but such query on the webs gets of course
>> 0001, 0002, 0003

>> I appreciate your answer.

>.

 
 
 

1. Games, Games, Games...

Hey Folks,

I just wanted to let everyone know that I have put a couple more games,
developed in FileMaker Pro, up on our GAMES page.

Feel free to visit our SITE and click the GAMES button (LINK) and
download them for free.  They are available in ".SIT" and ".ZIP" archive
formats.  The two NEW games are:

FLEA and FMP PONG

Although we are slowly building quite a collection to choose from:-)

BTW...while I'm on this subject, there are a few SITES out there that
are hosting my other games:

HANGMAN
ALTER-LINK
The MEMORY Game

The LATEST revisions are also on our GAMES page.  Feel free to UPDATE
them if you wish.  If you are in need of that extra FTP space, feel free
to link to our Home Page.

Enjoy!!!

Don Wieland
D W   D A T A   C O N C E P T S
~~~~~~~~~~~~~~~~~~~~~~~~~
HOME PAGE - http://members.home.net:80/dwieland/dwdchome.htm

Direct Line - (949) 786-9318

2. searching for JDBC-Driver

3. please help does game enhancer work with ALL games

4. Trie data structure..

5. Sql server database on the web playing strange games

6. Microsoft, Pervasive, Epicor... I can't tell which is worse...

7. 9$ PSX/SATURN GAME CD'S

8. Fixing Portal Rows

9. PRESS REL: IBM result's system for Olympic games

10. SQL server 2000: cancel query and now can't access database

11. SQL 6.5 Enterprise Query's locking the Database

12. SQL query's to a database

13. Querying SQL server 7.00 (MSDE) to list it's databases