SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: SELECT problem in MySQL...
-
May 13, 2003, 06:51 #1
- Join Date
- Oct 2001
- Location
- Belgium - Antwerp
- Posts
- 79
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
SELECT problem in MySQL...
Hi,
I want to obtain a certain result from my tables but I am unsuccesfull...
I use the alpha version 4.1 (subqueries possible)...
Ok, I have a table that contains games from chess players.
The name of that table is "games".
Some fields in that table are foreing keys.. among them :
- the field "nrw" (= number White)
- the field "nrb" (= number Black)
others are supplementary data, such as :
- the field "round" (= the round number when that game was played)
- the field "date" (= the date of the round played)
They both point to the primary key of the table "members" in wich their data is stored.
Now I have the following problem..
If I wish to obtain all the results of the games played by a certain player (in this example referenced by the id 38601) I execute the following SQL statement :
SELECT * FROM games WHERE nrw = 38601 OR nrb = 38601
Now, I wish to show all the rounds (1, 2, 3 etc.) and fill the rounds for wich he did not play with NULL values, the ones he did play contain the linked data of course..
But I don't seem te pull it off..
Of course, for rounds in wich he did not participate there are no rows so I understand that a simple SELECT cannot show a row wich is not there.. Therefore one needs joins maybe ? I only use one table..
So what I would like would be something like this (in case player '38601' only participated in the first round) :
Round, nrw, nrz, result
1, 38601, 47554, 1
2, NULL, NULL, NULL
Thx in advance
Greettzz
-
May 13, 2003, 09:27 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you need a left outer join from the rounds to the player's games
thus, all rounds will be listed, and if that player did not play in that round, the left outer join supplies the nulls for those columns
something like this --
select R.roundno
, G.nrw, G.nrz, G.result
from ( select distinct round from games ) R
left outer
join games G
on R.round = G.round
where ...
please let me know if mysql 4.1 supports that subquery syntax
rudy
-
May 20, 2003, 23:37 #3
- Join Date
- Oct 2001
- Location
- Belgium - Antwerp
- Posts
- 79
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks for the reply
However, it does not work for some reason.. The syntax is allowed but the query itself only produces rows for wich games are played.
Could this have to do with the table design or is it something else ?
If you need more information about my DB, I would be happy to provide you with it.
Thx in advance...
Greettzz
-
May 21, 2003, 05:30 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
please show a small number of sample rows (relevant columns only), and i'll give it a try
Bookmarks