我的任务是将数据插入到几个表中,并在一个查询中从它们的交集返回数据。这些表通过特定字段链接(solditems
指products
和invoices
、invoices
指customers
等)。这里的技巧是id
ininvoices
是在行插入时自动设置的。以下查询返回空结果,除非我删除JOIN
on invoices
。如果SELECT
在单独的查询中完成,则一切正常。怎么了?
WITH newid AS (
INSERT INTO invoices (customer,idate)
VALUES (777,(SELECT now()::date))
RETURNING id
),
sold AS (
INSERT INTO solditems (invoiceid,prod,qty)
VALUES ((SELECT id FROM newid), 888, 1),
((SELECT id FROM newid), 999, 2)
RETURNING *
)
SELECT * FROM sold AS s
JOIN products AS p ON p.id=s.prod
JOIN invoices AS i ON i.id=s.invoiceid;