2表查询专用协议

Question

我有一个搜索结果查询，我需要修改，这样它就不会显示已经售出的产品。下面是当前存在的未修改的select语句：

SELECT id, cid, sid, posttitle, postdetails, city, `state`, zipcode 
FROM posts 
ORDER BY datepublished DESC

我需要修改它，以便它看起来像orderdetails table underDetailProductID`‘，看看它是否存在，现在是否存在；因此，在英语中，本质上应该是这样的：

SELECT id, cid, **etc** 
FROM posts 
WHERE posts.id NOT IN orderdetails.DetailProductID

Answer 1

SELECT 
id, cid, sid, posttitle, postdetails, city, state, zipcode 
FROM posts 
where posts.id not in (select DetailProductID from orderdetails)
ORDER BY datepublished DESC

或者使用巧妙的左联接

SELECT 
posts.id, posts.cid, posts.sid, posts.posttitle, posts.postdetails, posts.city, posts.state, posts.zipcode 
FROM posts 
LEFT JOIN orderdetails ON orderdetails.DetailProductID = posts.id
where orderdetails.DetailProductID is null
ORDER BY posts.datepublished DESC

Answer 2

SELECT id, cid, etc from posts WHERE posts.id NOT IN (Select DetailProductID from orderdetails)