MYSQL - 帮助加入仅 1 行

Question

我当前返回了不止一行，因此我尝试使用此连接来连接最新的项目。

LEFT OUTER JOIN wp_posts wp_posts1 ON (wp_posts.ID = (SELECT MAX(post_parent) FROM wp_posts1 WHERE wp_posts1.post_parent = wp_posts.ID) ) 

但我收到错误“SQL 错误:表 'web13-a-tablename.wp_posts1' 不存在”。

下面我的原始代码返回了太多结果。

SELECT 
wp_posts.ID, 
wp_posts.post_title, 
wp_posts1.guid AS f_image,
wp_posts1.id AS f_ID 
FROM wp_posts 
LEFT OUTER JOIN wp_posts wp_posts1 ON (wp_posts.ID = wp_posts1.post_parent) AND wp_posts1.post_type = 'attachment'  
WHERE wp_posts.post_type = 'custom' 
AND wp_posts.post_status = 'publish' 
AND (wp_posts.post_title LIKE '%smith%' OR wp_posts.post_title LIKE '%jones%')
ORDER BY 
f_ID DESC

SQLFiddle 显示选定的多行。我只想获取一行，并且我想选择最大的 f_ID。 http://sqlfiddle.com/#!2/97ee9/3/0

所需结果:

ID       POST_TITLE       F_IMAGE       F_ID
--------------------------------------------
2365     John Jones       IMG_URL       2490
2385     Becky Smith      IMG_URL       2390

Answer 1

将子查询更改为:

SELECT MAX(post_parent) FROM wp_posts as w3 WHERE w3.post_parent = wp_posts.ID

更新将 fiddle 中的查询更改为:

SELECT 
  wp_posts.ID, 
  wp_posts.post_title, 
  wp_posts1.guid AS f_image,
  MAX(wp_posts1.id) AS f_ID 
FROM wp_posts 
LEFT OUTER JOIN wp_posts wp_posts1 ON (wp_posts.ID = wp_posts1.post_parent) AND wp_posts1.post_type = 'attachment'  
WHERE wp_posts.post_type = 'custom' 
  AND wp_posts.post_status = 'publish' 
  AND (wp_posts.post_title LIKE '%smith%')
GROUP BY
  wp_posts.ID