开发者

Selecting NULL columns

I have written a query and it is selecting columns that are sometimes NULL.

$q = "SELECT g.id AS gameid,g.home_user, g.home_user2, g.away_user, g.away_user2, g.home_score, g.away_score, g.date_confirmed, g.type,
       开发者_运维百科   h1.username AS home_username, h2.username AS home_username2, a1.username AS away_username2, a2.username AS away_username2
          FROM ".TBL_FOOT_GAMES." g 
          INNER JOIN ".TBL_USERS." h1 ON h1.id = g.home_user
          INNER JOIN ".TBL_USERS." h2 ON h2.id = g.home_user2
          INNER JOIN ".TBL_USERS." a1 ON a1.id = g.away_user
          INNER JOIN ".TBL_USERS." a2 ON a2.id = g.away_user2
          WHERE g.status = '$status'
          ORDER BY g.date_confirmed DESC LIMIT 25";

The columns

home_user2 and away_user2

can be NULL sometimes. Obviously this seems to be stopping the query from showing any returned data. When i removed those columns from the query, I got a return. Including them isn't throwing an error, just simple isn't selecting any rows.

How can I get around this?


You will need to use a left join, as opposed to an inner join on those columns.

$q = "SELECT g.id AS gameid,g.home_user, g.home_user2, g.away_user, g.away_user2, g.home_score, g.away_score, g.date_confirmed, g.type,
          h1.username AS home_username, h2.username AS home_username2, a1.username AS away_username2, a2.username AS away_username2
          FROM ".TBL_FOOT_GAMES." g 
          INNER JOIN ".TBL_USERS." h1 ON h1.id = g.home_user
          LEFT JOIN ".TBL_USERS." h2 ON h2.id = g.home_user2
          INNER JOIN ".TBL_USERS." a1 ON a1.id = g.away_user
          LEFT JOIN ".TBL_USERS." a2 ON a2.id = g.away_user2
          WHERE g.status = '$status'
          ORDER BY g.date_confirmed DESC LIMIT 25";
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜