(不好意思,之前的问题表述不准。看到评论和回答里面用的都是left join,这个是我的问题,再次深表歉意!)
这条语句想要达到的目的是:根据activity_relation
表中activity_id
和tag_id
的关系,在activity
表中取出拥有指定tag的文章。
SELECT DISTINCT * FROM `activity` WHERE status != 'draft' JOIN `activity_relation` ON `activity.id` = `activity_relation.activity_id` AND (`tag_id` = '10')
现在MySQL的错误提示是:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON ji_activity.id
= ji_activity_relation.activity_id
AND (tag_id
= '10'))' at line 1
谁能帮我看一下是哪里出了问题?谢谢。
- 首先,你需要明确sql语句的执行顺序,from子句组合来自不同数据源的数据,where子句对指定条件进行筛选,如果还需要组合的话,需要考虑group by和having子句
- 其次,回答一下你的sql语句错误,你应该先用from和join将两个表组合,再用where子句筛选
select distince a.* from activity as a join activity_relation as b on a.id = b.activity_id where a.status != 'draft' and b.tag_id = '10';
肿么会写成那样。
SELECT a.* FROM activity_relation r LEFT JOIN activity a
ON r.activity_id = a.id WHERE a.status != 'draft' AND r.tag_id = 10