I'm having the strangest result with a query on across several tables. I'd be most grateful for any insight! Thanks!

-- Andrew

The table gameResults has a primary key userGameEventID

  INTEGER (11) NOT NULL AUTO_INCREMENT

This is an integer, so I don't expect to need to put a quote around the value in my query, but when I exclude the quotes from the very last word in following query, I get zero records back:

  SELECT * FROM gameResults, games, users, questionResults, questions
  WHERE gameResults.gameID = games.gameID
  AND gameResults.userID = users.userID
  AND gameResults.userGameEventID = questionResults.userGameEventID
  AND questions.gameID = gameResults.gameID
  AND questionResults.quesID = questions.quesID
  AND gameResults.userGameEventID = '3'

i.e: if the last line is just

  AND gameResults.userGameEventID = 3

then I get zero results.

Also very strange, and even more troubling, since I don't have a work-around: if I try to specify

  ORDER BY questions.quesID

I also get zero records.


Below is the structure of the tables in the query:

    // Table structure for table 'users'
    $sql = "CREATE TABLE users (
        userID char(50) NOT NULL default '',
        firstName char(20) default NULL,
        lastName char(20) default NULL,
        password char(20) default NULL,
        status char(20) NOT NULL default '',
        KEY status (status),
        KEY userID (userID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);

    // Table structure for table 'games'
    $sql = "CREATE TABLE games (
        gameFile varchar(50) default NULL,
        gameFolder tinytext,
        description text,
        allowAll tinyint(3) unsigned NOT NULL default '0',
        allowRepeat tinyint(3) unsigned NOT NULL default '0',
        allowResultsView tinyint(3) unsigned NOT NULL default '0',
        allowQuestionReview tinyint(3) unsigned NOT NULL default '0',
        allowCorrectView tinyint(3) unsigned NOT NULL default '0',
        showResultsAfter tinyint(3) unsigned NOT NULL default '0',
        allowAnonAccess tinyint(3) unsigned NOT NULL default '0',
        gameID int(11) NOT NULL auto_increment,
        PRIMARY KEY (gameID),
        UNIQUE KEY gameID (gameID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);

    // Table structure for table \"questions\"
    $sql = "CREATE TABLE questions (
        gameID int(11) NOT NULL default '0',
        quesID char(10) default '',
        category char(100) default '',
        type char(10) default '',
        format char(10) default '',
        pointValue smallint(6) NOT NULL default '0',
        stem char(255) default '',
        respCount tinyint(4) default '0',
        resp1 char(255) default '',
        resp2 char(255) default '',
        resp3 char(255) default '',
        resp4 char(255) default '',
        correct1 tinyint(4) default '0',
        correct2 tinyint(4) default '0',
        correct3 tinyint(4) default '0',
        correct4 tinyint(4) default '0',
        KEY quesID (quesID),
        KEY gameID (gameID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);


    // Table structure for table 'gameResults'
    $sql = "CREATE TABLE gameResults (
        gameID int(11) NOT NULL default '0',
        userGameEventID int(11) NOT NULL auto_increment,
        userID char(50) NOT NULL default '',
        startTime datetime default NULL,
        endTime datetime default NULL,
        score mediumint(9) NOT NULL default '0',
        pointsEarned mediumint(9) NOT NULL default '0',
        pointsLost mediumint(9) NOT NULL default '0',
        questionsCount smallint(6) NOT NULL default '0',
        seenCount smallint(6) NOT NULL default '0',
        answeredCount smallint(6) NOT NULL default '0',
        correctCount smallint(6) NOT NULL default '0',
        PRIMARY KEY (userGameEventID),
        KEY score (score),
        KEY gameID (gameID),
        KEY userID (userID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);


    // Table structure for table 'questionResults'
    $sql = "CREATE TABLE questionResults (
        userGameEventID int(11) NOT NULL default '0',
        quesID char(10) default '',
        seen tinyint(4) NOT NULL default '0',
        answered tinyint(4) NOT NULL default '0',
        correct tinyint(4) NOT NULL default '0',
        award smallint(6) NOT NULL default '0',
        answer smallint(6) NOT NULL default '0',
        KEY quesID (quesID),
        KEY userGameEventID (userGameEventID)
    ) TYPE=MyISAM;";
    $result = mysql_query($sql);

I fixed the problem by specifying a LEFT JOIN for each table relationship. I don't recall why I wasn't doing this before. I think it was because I learned how to write SQL using Access with ASP, where nested SELECTs are allowed. When I migrated to MySQL/PHP, I had to learn a whole new way to write these complex JOINs. At the time I thought I would never get along without nested SELECTs.

Here's my new FROM statement

SELECT * FROM
gameResults LEFT JOIN games ON gameResults.gameID = games.gameID
LEFT JOIN users ON gameResults.userID = users.userID
LEFT JOIN questionResults ON gameResults.userGameEventID = questionResults.userGameEventID
LEFT JOIN questions ON (gameResults.gameID = questions.gameID AND questionResults.quesID = questions.quesID)
WHERE gameResults.gameID = 3
GROUP BY questions.quesID