Mysql query help - Alter this mysql query to get these results?

Question

Please execute the following queries first to set up so that you can help me:-

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

• In the present system data which I have given , tutor named "Sandeepan Nath" has has created class named "My Class" and tutor named "Bob Cratchit" has created class named "Sandeepan Class".

Requirement -

To execute a single query with limit on the results to show search results as per AND logic on the search keywords like this:-

1. If "Sandeepan Class" is searched , Tutor Sandeepan Nath's record from Tutor Details table is returned(because "Sandeepan" is the firstname of Sandeepan Nath and Class is present in class name of Sandeepan's class)
2. If "Class" is searched Both the tutors from the Tutor_details table are fetched because Class is present in the name of the class created by both the tutors.

Following is what I have so far achieved (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>";
}

}

}
?>

But the results are :-

If "Sandeepan Nath" is searched, it does not return any tutor (instead of only Sandeepan's row) If "Sandeepan Class" is searched, it returns Sandeepan's row (instead of Both tutors ) If "Bob Class" is searched, it correctly returns Bob's row If "Bob Cratchit" is searched, it does not return any tutor (instead of only

Answer 1

Problem is that you have 2 search terms and you're not generating any rows in which you can search for two tags from the same relations table (this is easy to see if you look at the results from your query without restricting them to td,*). Solution, if you want to do it in SQL, is to generate all 2 search term permutations of the tags used for each tutor/class relation (again, that explanation makes a lot more sense when you look at the full query results). Anyhow, here's my take on fixing SQL the way you're doing it:

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

This really isn't how I'd go about it though. Things will get very, very heavy trying to do this kind of searching through joins and will only get worse if you add more search terms.

Explanation of missing permutation:

These tables are produced by removing the where clause, the group clause, removing duplicates and only showing the td1 and td2 columns.

Your way:

+--------+-----------+--------+-----------+
| 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      |
+--------+-----------+--------+-----------+

Now if we look at this we see that td1.id_tag is produced from either the class or tutors relations present. Also td2.id_Tag is produced from either the class or tutor relations present. However, for any 1 row of this result td1.id_Tag and td2.id_tag cannot be from the same relations table. They are always Class/Tutors or Tutors/Class there is never a row for a Class/Class or Tutors/Tutors set of tags (remember that there is a Sandeepan tag in the Class relations table). Which means there's no way for you to search "Sandeepan" "Nash" or "Bob" "Cratchit" because in both cases those tags are only present in one table.

My way:

+--------+-----------+--------+-----------+
| 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        |
+--------+-----------+--------+-----------+

All my SQL does is produce the missing Class/Class Tutors/Tutors rows ,which fixes the issue.