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 :
I get all the data from the rounds in wich he played.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) :
I really don't see it..Round, nrw, nrz, result
1, 38601, 47554, 1
2, NULL, NULL, NULL
Thx in advance