SQL JOIN:只是无法理解它们 [英] SQL JOIN: Just not able to understand them

问题描述

现在，我知道这个与JOIN有关的问题已经被问过很多次了.我经历了很多.但是我仍然不清楚.我也阅读了这些文章: http://www.codeproject .com/Articles/33052/Visual-Representation-of-SQL-Joins#_comments 和

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).

现在，有两个问题:

1. 此查询正确吗?会给我想要的结果吗?

1. 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:只是无法理解它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！