SELECT problem in MySQL...

[Quin]

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

I get all the data from the rounds in wich he played.

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

I really don't see it..

Thx in advance

Greettzz

[r937]

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

[Quin]

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

[r937]

please show a small number of sample rows (relevant columns only), and i'll give it a try