SQL-带JOIN的WHERE子句 [英] SQL - WHERE clause with JOIN

问题描述

mysql_query("SELECT a.about,a.user,b.user,b.title,b.article,b.description  
FROM about a 
JOIN articles b ON b.user_id=a.user_id
WHERE MATCH(b.title,b.article) AGAINST ('$search')
");

我需要扩展此查询以包括something like " AND user='$user'"，但要同时检查两个'a' and 'b'表.

I need to expand this query to include something like " AND user='$user'" but where both 'a' and 'b' tables are checked.

我可以做"AND a.user='$user' AND b.user='$user'"，但是我将再添加18个表，因此看起来有些肿.

I could do "AND a.user='$user' AND b.user='$user'" but I will be adding 18 more tables so this is looking a bit bloated.

有什么方法可以使事情更紧凑?

Is there any way to make things more compact?

推荐答案

您只需要检查一个表上的用户名，而不必同时检查两个表，因为使用user_id的INNER JOIN已经将它们匹配了.

You should only need to check the username on one table not both as the INNER JOIN using the user_id has already matched them up.

说实话，表b不应真正包含用户字段，而应仅具有user_id字段(这被归一化).

To be honest table b shouldn't really have a user field, instead it should just have the user_id field (this is considered normalised).

然后，INNER JOIN将仅返回两个表中都匹配的数据.因此，将表a.user_id与表b.user_id连接起来可以确保它们都具有匹配的user_id.

An INNER JOIN will then only return data where there is a match in both tables. So joining Table a.user_id to Table b.user_id ensures they both have a matching user_id.

这篇关于SQL-带JOIN的WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！