问题描述
现在,我知道这个与JOIN
有关的问题已经被问过很多次了.我经历了很多.但是我仍然不清楚.我也阅读了这些文章: http://www.codeproject .com/Articles/33052/Visual-Representation-of-SQL-Joins#_comments 和 http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html ,但没有,仍然没有帮助.
Now, I know know this question related to JOIN
have been asked many times. I went through many of them. But it still isn't clear to me. I read these aricles too: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins#_comments and http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html but no, it still didn't help.
我确实在数学上理解了静脉图,但无法掌握JOIN
背后的基本概念.
I do understand the Vein diagrams mathematically but not able to grab basic concept behind JOIN
.
假设我有两个表.
tbl_book说明:
tbl_bookdescription:
| BOOKID | BOOKNAME | BOOKREVIEW | AUTHORID |
------------------------------------------------
| 1 | SQL Basics | Cool | 1 |
| 2 | PHP Basics | Good | 2 |
| 3 | AJAX Basics | Superb | 2 |
| 4 | HTML Basics | Very Good | 3 |
tbl_authordescription
| AUTHORID | AUTHORNAME |
-------------------------
| 1 | Tom |
| 2 | Jerry |
| 3 | Phil |
我想为我的网站编写一个搜索引擎脚本
I want to script a search engine for my website
因此,当用户输入Tom
作为$searchTerm
时,我希望程序返回由Tom
编写的书的名称.同时,用户还可以输入Good
.这次查询应再次返回书名.所以,我想做这样的事情
So, when the user enters Tom
as $searchTerm
, I want the program to return the name of the book which is written by Tom
. And at the same time, the user can also enter Good
. This time the query should again return the name of the book. So, I thought to do something like this
SELECT bookname FROM tbl_bookdescription MATCH(bookReview) AGAINST('$searchTerm')`
,然后UNION
将该表与SOMETHING
(将authorName与$ searchterm相匹配的内容).
and then UNION
this table with SOMETHING
(something which matches authorName against $searchterm).
现在,有两个问题:
-
此查询正确吗?会给我想要的结果吗?
Is this query right? Will it give me the desired results?
WHat应该代替SOMETHING
编写代码.我想我将必须同时对两个表进行JOIN
(不确定).而且不知道我应该如何加入.
WHat should I write in the code in place of SOMETHING
. I think I will have to JOIN
both the tables(not sure). And don't know how should I join.
帮助表示赞赏.
推荐答案
如果仅使用一个搜索词进行搜索,则查询看起来像
If you search using only one search term then your query might look like
SELECT b.*, a.*
FROM tbl_bookdescription b JOIN tbl_authordescription a
ON b.authorID = a.authorID
WHERE b.bookName LIKE '%searchterm%'
OR b.bookReview LIKE '%searchterm%'
OR a.authorName LIKE '%searchterm%'
如果将"searchterm
"替换为"Tom",您将得到
If you replace searchterm
with 'Tom' you'll get
| BOOKID | BOOKNAME | BOOKREVIEW | AUTHORID | AUTHORNAME |
------------------------------------------------------------
| 1 | SQL Basics | Cool | 1 | Tom |
现在,如果它很好,那么
Now, if it's 'Good' then
| BOOKID | BOOKNAME | BOOKREVIEW | AUTHORID | AUTHORNAME |
-------------------------------------------------------------
| 2 | PHP Basics | Good | 2 | Jerry |
| 4 | HTML Basics | Very Good | 3 | Phil |
这里是 SQLFiddle 演示
Here is SQLFiddle demo
这篇关于SQL JOIN:只是无法理解它们的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!