MySQL查询帮助 - 改变这个MySQL查询得到这些结果？

Question

请先执行下面的查询来设置，这样就可以帮助我： -

CREATE TABLE IF NOT EXISTS `Tutor_Details` (
`id_tutor` int(10) NOT NULL auto_increment, 
`firstname` varchar(100) NOT NULL default '', 
`surname` varchar(155) NOT NULL default '', 
PRIMARY KEY (`id_tutor`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ; 

INSERT INTO `Tutor_Details` (`id_tutor`,`firstname`, `surname`) VALUES 
(1, 'Sandeepan', 'Nath'), 
(2, 'Bob', 'Cratchit'); 

CREATE TABLE IF NOT EXISTS `Classes` (
`id_class` int(10) unsigned NOT NULL auto_increment, 
`id_tutor` int(10) unsigned NOT NULL default '0', 
`class_name` varchar(255) default NULL, 
PRIMARY KEY (`id_class`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=229 ; 

INSERT INTO `Classes` (`id_class`,`class_name`, `id_tutor`) VALUES 
(1, 'My Class', 1), 
(2, 'Sandeepan Class', 2); 

CREATE TABLE IF NOT EXISTS `Tags` (
`id_tag` int(10) unsigned NOT NULL auto_increment, 
`tag` varchar(255) default NULL, 
PRIMARY KEY (`id_tag`), 
UNIQUE KEY `tag` (`tag`), 
KEY `id_tag` (`id_tag`), 
KEY `tag_2` (`tag`), 
KEY `tag_3` (`tag`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ; 

INSERT INTO `Tags` (`id_tag`, `tag`) VALUES 
(1, 'Bob'), 
(6, 'Class'), 
(2, 'Cratchit'), 
(4, 'Nath'), 
(3, 'Sandeepan'), 
(5, 'My'); 

CREATE TABLE IF NOT EXISTS `Tutors_Tag_Relations` (
`id_tag` int(10) unsigned NOT NULL default '0', 
`id_tutor` int(10) default NULL, 
KEY `Tutors_Tag_Relations` (`id_tag`), 
KEY `id_tutor` (`id_tutor`), 
KEY `id_tag` (`id_tag`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

INSERT INTO `Tutors_Tag_Relations` (`id_tag`, `id_tutor`) VALUES 
(3, 1), 
(4, 1), 
(1, 2), 
(2, 2); 

CREATE TABLE IF NOT EXISTS `Class_Tag_Relations` (
`id_tag` int(10) unsigned NOT NULL default '0', 
`id_class` int(10) default NULL, 
`id_tutor` int(10) NOT NULL, 
KEY `Class_Tag_Relations` (`id_tag`), 
KEY `id_class` (`id_class`), 
KEY `id_tag` (`id_tag`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

INSERT INTO `Class_Tag_Relations` (`id_tag`, `id_class`, `id_tutor`) VALUES 
(5, 1, 1), 
(6, 1, 1), 
(3, 2, 2), 
(6, 2, 2); 

• 在本系统中的数据，我给了，名为“Sandeepan纳特”导师有已创建的类名为“我的班”和导师名为‘鲍伯’已创建名为类‘Sandeepan类’。

要求 -

要对结果有限制执行一个查询来显示搜索结果按照与逻辑上是这样的搜索关键字： -

1. 如果“Sandeepan班”是（因为“Sandeepan”是Sandeepan Nath的名字，Class在Sandeepan的班级名称中存在）
2. 如果搜索到“Class”这两个Tutor_details表中的导师都将被返回（因为“Sandeepan”是Sandeepan Nath的名字）被提取因为Class是以两个导师创建的班级的名字出现的。

以下是我迄今为止所取得（PHP MySQL的）： -

<?php 
$searchTerm1 = "Sandeepan"; 
$searchTerm2 = "Class"; 

mysql_select_db("test"); 


$sql = "SELECT td.* 
FROM Tutor_Details AS td 
LEFT JOIN Tutors_Tag_Relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor 
LEFT JOIN Classes AS wc ON td.id_tutor = wc.id_tutor 
LEFT JOIN Class_Tag_Relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor 

LEFT JOIN Tags as t1 on ((t1.id_tag = ttagrels.id_tag) OR (t1.id_tag = wtagrels.id_tag)) 
LEFT JOIN Tags as t2 on ((t2.id_tag = ttagrels.id_tag) OR (t2.id_tag = wtagrels.id_tag)) 


where t1.tag LIKE '%".$searchTerm1."%' 
AND t2.tag LIKE '%".$searchTerm2."%' 

GROUP BY td.id_tutor 
LIMIT 10 
"; 

$result = mysql_query($sql); 
echo $sql; 
if($result) 
{ 

while($rec = mysql_fetch_object($result)) $recs[] = $rec; 
//$rec = mysql_fetch_object($result); 
echo "<br><br>"; 

if(is_array($recs)) 
{ 
foreach($recs as $each) 
{ 
print_r($each); 
echo "<br>"; 
} 

} 

} 
?> 

但结果是： -

如果 “Sandeepan纳特” 进行搜索，它不返回任何如果搜索到“Sandeepan Class”，则返回Sandeepan的行（而不是两个导师） 如果搜索到“Bob Class”，它会正确返回Bob的行 如果搜索到“Bob Cratchit”它不会返回任何导师（而不是onl Ÿ

Answer 1

问题是，你有2个搜索词和你不是产生在其中您可以搜索来自同一个关系表中的两个标签（这是很容易看到，如果你看一下从您查询的结果没有任何行限制他们td，*）。解决办法，如果你想这样做的SQL，是产生用于每位教师/类的关系（同样，这种解释让很多更有意义，当你看完整的查询结果）标签的所有2搜索词的排列。无论如何，这里是我采取修复SQL的方式，你这样做：

SELECT td.* 
FROM Tutors_Tag_Relations AS ttagrels1 
JOIN Tutors_Tag_Relations AS ttagrels2 ON 
    ttagrels2.id_tutor = ttagrels1.id_tutor AND 
    ttagrels2.id_tag != ttagrels1.id_tag 
JOIN Class_Tag_Relations AS wtagrels1 ON 
    wtagrels1.id_tutor = ttagrels1.id_tutor AND 
    wtagrels1.id_tag != ttagrels1.id_tag AND 
    wtagrels1.id_tag != ttagrels2.id_tag 
JOIN Class_Tag_Relations AS wtagrels2 ON 
    wtagrels2.id_tutor = ttagrels1.id_tutor AND 
    wtagrels2.id_tag != wtagrels1.id_tag AND 
    wtagrels2.id_tag != ttagrels1.id_tag AND 
    wtagrels2.id_tag != ttagrels2.id_tag 
JOIN Tags as t1 ON 
    t1.id_tag = ttagrels1.id_tag OR 
    t1.id_tag = ttagrels2.id_tag OR 
    t1.id_tag = wtagrels1.id_tag OR 
    t1.id_tag = wtagrels2.id_tag 
JOIN Tags as t2 ON 
    t2.id_tag != t1.id_tag AND 
    (t2.id_tag = ttagrels1.id_tag OR 
    t2.id_tag = ttagrels2.id_tag OR 
    t2.id_tag = wtagrels1.id_tag OR 
    t2.id_tag = wtagrels2.id_tag) 
LEFT JOIN Tutor_Details as td ON ttagrels1.id_tutor = td.id_tutor 
LEFT JOIN Classes AS wc ON td.id_tutor = wc.id_tutor 
WHERE 
    t1.tag LIKE '%Sandeepan%' AND 
    t2.tag LIKE '%Nath%' 
GROUP BY td.id_tutor 

这真的不是我如何去了解它。事情会变得非常，非常沉重试图做这种通过加入搜索的，如果你添加更多的搜索条件只会变得更糟。

缺失排列的说明：

这些表通过去除where子句，该组子句，删除重复且仅示出了TD1和TD2的列产生的。

你的道：

+--------+-----------+--------+-----------+ 
| id_tag | tag  | id_tag | tag  | 
+--------+-----------+--------+-----------+ 
|  1 | Bob  |  3 | Sandeepan | 
|  1 | Bob  |  6 | Class  | 
|  2 | Cratchit |  3 | Sandeepan | 
|  2 | Cratchit |  6 | Class  | 
|  3 | Sandeepan |  1 | Bob  | 
|  3 | Sandeepan |  2 | Cratchit | 
|  3 | Sandeepan |  5 | My  | 
|  3 | Sandeepan |  6 | Class  | 
|  4 | Nath  |  5 | My  | 
|  4 | Nath  |  6 | Class  | 
|  5 | My  |  3 | Sandeepan | 
|  5 | My  |  4 | Nath  | 
|  6 | Class  |  1 | Bob  | 
|  6 | Class  |  2 | Cratchit | 
|  6 | Class  |  3 | Sandeepan | 
|  6 | Class  |  4 | Nath  | 
+--------+-----------+--------+-----------+ 

现在，如果我们看看这个我们看到td1.id_tag从目前无论是类或导师的关系产生的。此外td2.id_Tag是由班级或导师关系出示的。然而，对于任何1行这一结果td1.id_Tag和td2.id_tag不能来自同一关系表。它们始终是Class/Tutors或Tutors/Class，对于Class/Class或Tutors/Tutors标签而言，永远不会有行（请记住Class关系表中存在Sandeepan标签）。这意味着你无法搜索“Sandeepan”，“Nash”或“Bob”“Cratchit”，因为在这两种情况下，这些标签只出现在一张表中。

我的方式：

+--------+-----------+--------+-----------+ 
| id_tag | tag  | id_tag | tag  | 
+--------+-----------+--------+-----------+ 
|  1 | Bob  |  2 | Cratchit | 
|  1 | Bob  |  3 | Sandeepan | 
|  1 | Bob  |  6 | Class  | 
|  2 | Cratchit |  1 | Bob  | 
|  2 | Cratchit |  3 | Sandeepan | 
|  2 | Cratchit |  6 | Class  | 
|  3 | Sandeepan |  1 | Bob  | 
|  3 | Sandeepan |  2 | Cratchit | 
|  3 | Sandeepan |  4 | Nath  | 
|  3 | Sandeepan |  5 | My  | 
|  3 | Sandeepan |  6 | Class  | 
|  4 | Nath  |  3 | Sandeepan | 
|  4 | Nath  |  5 | My  | 
|  4 | Nath  |  6 | Class  | 
|  5 | My  |  3 | Sandeepan | 
|  5 | My  |  4 | Nath  | 
|  5 | My  |  6 | Class  | 
|  6 | Class  |  1 | Bob  | 
|  6 | Class  |  2 | Cratchit | 
|  6 | Class  |  3 | Sandeepan | 
|  6 | Class  |  4 | Nath  | 
|  6 | Class  |  5 | My  | 
+--------+-----------+--------+-----------+ 

我所有的SQL确实是产生缺失的类/班主任/导师行，其修复该问题。