我的应用程序有一个可以预订的用户列表。我们需要向用户支付这些预订费。我需要得到需要付费的用户列表。如果用户有预订或创建了没有预订的付款，则可以向用户付款。
我试了一下：
SELECT
users.id as user_id,
user_bookings.id as user_booking_id,
user_bookings.payment_id as user_booking_payment_id,
payments.id as payment_id
FROM
users
LEFT OUTER JOIN user_bookings ON user_bookings.user_id = user.id
FULL JOIN payments ON payments.id = user_bookings.payment_id
Where payments.issued = false;
但它没有列出没有预订的付费用户。它只列出已创建预订的用户。我如何才能得到用户的付款，没有任何预订？
编辑
我尝试了以下查询：
拥有37271的用户具有以下付款记录：
select payments.id, payments.issued from payments where payments.user_id=37271;
id | issued
--------+--------
133046 | f
133045 | t
(2 rows)
以下是我的预订情况：
select user_bookings.id, user_bookings.payment_issued, user_bookings.payment_id from user_bookings where user_id=37271;
id | payment_issued | payment_id
--------+-------------------+---------------
541136 | t | 133045
541137 | t | 133045
(2 rows)
运行以下查询时：
SELECT u.id as user_id, ub.id as user_booking_id, ub.payment_id as user_booking_payment_id,
p.id as payment_id, p.issued as payments_issued
FROM users u LEFT OUTER JOIN
user_bookings ub
ON ub.user_id = u.id FULL JOIN
(SELECT p.*
FROM payments p
WHERE NOT p.issued
) p
ON p.id = ub.payment_id
where users.id=37271;
它不返回没有预订的付款，但返回已发出的付款：
user_id | user_booking_id | user_booking_payment_id | payment_id | payments_issued
---------+-----------------+-------------------------+------------+-----------------
37271 | 541137 | 133045 | |
37271 | 541136 | 133045 | |
(2 rows)
最佳答案
SELECT
users.id as user_id,
user_bookings.id as user_booking_id,
user_bookings.payment_id as user_booking_payment_id,
payments.id as payment_id,
FROM
users
LEFT OUTER JOIN user_bookings ON user_bookings.user_id = user.id
FULL JOIN payments ON payments.id = linguist_bookings.payment_id
Where payments.issued = false
OR payments.issued IS NULL;
如果使用外部联接，如果在
WHERE子句中使用它，则将其转换为内部联接；因为NULL = [something]从来都不是真的。