LEFT/RIGHT JOIN不返回空行 [英] LEFT/RIGHT JOIN not returning empty row
本文介绍了LEFT/RIGHT JOIN不返回空行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个查询,该查询在2个表上使用自联接以返回单行结果.我的问题是,如果seat1为空,我什么也得不到.
I have a query which uses a self join on 2 tables to return a single row of results. The problem I have is that if seat1 is empty, I don't get anything returned.
SELECT seat1.seat_type_id
, seat1.seat_type_qty
, seat2.seat_type_id
, seat2.seat_type_qty
FROM jos_sv_apptpro2_requests AS R
LEFT JOIN jos_sv_apptpro2_seat_counts AS seat1 ON R.id_requests = seat1.request_id
LEFT JOIN jos_sv_apptpro2_seat_counts AS seat2 ON R.id_requests = seat2.request_id
WHERE (seat1.seat_type_id = 6 AND seat2.seat_type_id = 7)
AND R.id_requests = 8703
AND R.resource = 3
这应该返回:
seat_type_id 6
seat_type_qty 0 <= this is the empty row
seat_type_id1 7
seat_type_qty 1
我现在有:
SELECT
IFNULL(seat1.seat_type_qty,0) AS seat_type_qty,
IFNULL(seat2.seat_type_qty,0) AS seat_type_qty
FROM jos_sv_apptpro2_requests AS R
LEFT JOIN jos_sv_apptpro2_seat_counts AS seat1
ON R.id_requests = seat1.request_id AND seat1.seat_type_id = 6
LEFT JOIN jos_sv_apptpro2_seat_counts AS seat2
ON R.id_requests = seat2.request_id AND seat2.seat_type_id = 7
WHERE R.id_requests = 8696
重新编辑! -我的错误是,我在PHP中两次返回seat_type_qty作为对象.全部排序-谢谢!
REEDIT! - My mistake, I'm returning seat_type_qty twice as an object in my PHP. All sorted now - THANKS!
推荐答案
WHERE seat1.seat_type_id = 6 AND seat2.seat_type_id = 7
删除所有带有NULL值的行.您应该将这些条件移到JOIN条件,以便RDBMS达到您的期望:
is what removes all rows with NULL values. You should move those condition to the JOIN criteria so that the RDBMS does what you are expecting:
LEFT JOIN jos_sv_apptpro2_seat_counts AS seat1 ON R.id_requests = seat1.request_id
AND seat1.seat_type_id = 6
LEFT JOIN jos_sv_apptpro2_seat_counts AS seat2 ON R.id_requests = seat2.request_id
AND seat2.seat_type_id = 7
这篇关于LEFT/RIGHT JOIN不返回空行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文