SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Belgium - Antwerp
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    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

  3. #3
    SitePoint Enthusiast
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •