有4张桌子。我想将所有表与当前 ID 链接起来。如果我查询的最后一个表中没有值,则数据不会返回。我想访问所有表中的数据。我应该如何进行?
我的代码:(部分工作)
SELECT
U.id AS id,
U.amount AS amount,
A.commission AS commission,
I.power AS item_power
FROM affiliate AS A
INNER JOIN users AS U
ON A.owner_id = U.id
INNER JOIN users_booster AS B
ON U.id = B.userid
INNER JOIN auction_item AS I
ON B.item_id = I.id
WHERE
A.partner_id = '1947' AND
I.type = 'reference_booster' AND
B.end_time >= NOW()
GROUP BY I.type
使用left join
. 无group by
需要:
SELECT U.id AS id, U.amount AS amount, A.commission AS commission,
I.power AS item_power
FROM affiliate A JOIN
users U
ON A.owner_id = U.id JOIN
users_booster B
ON U.id = B.userid LEFT JOIN
auction_item I
ON B.item_id = I.id AND I.type = 'reference_booster'
WHERE A.partner_id = 1947 AND
B.end_time >= NOW();
笔记:
LEFT JOIN
时,你要保持行,即使没有匹配。LEFT JOIN
需要在ON
子句中。partner_id
是一个数字,所以不要将它与字符串进行比较。使用1947
代替'1947'
。当然,如果是字符串,保留单引号。GROUP BY
没有聚合函数很少有意义。本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句