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