获取查询结果行是否包含SELECT字段

Question

The query below returns rows that have both loginid and ip2 in the bumps table.

All rows in the bumps table have ip2, but only some have loginid.

How can I get the query below to return ip2 for all rows and loginid for the rows that have loginid?

$sqlStrend = "SELECT e.loginid, e.time, l.username, e.ip2
      FROM bumps e
      JOIN login l ON e.loginid = l.loginid
     WHERE e.submissionid = '$submissionid' 
  ORDER BY e.time DESC
  LIMIT $offset, $rowsperpage";

Answer 1

Rather than an INNER JOIN (implied by JOIN), you just need to change this to a LEFT JOIN, which returns all rows on the left-hand table and those related or NULL where rows in the related table do not exist.

SELECT e.loginid, e.time, l.username, e.ip2
FROM bumps e
LEFT JOIN login l ON e.loginid = l.loginid
WHERE e.submissionid = '$submissionid' 
ORDER BY e.time DESC
LIMIT $offset, $rowsperpage

Review the different JOIN types in this excellent Wikipedia article or Jeff Atwood's Visual Explanation of Joins.