Mysql - Help me alter this search query to get desired results

Question

Following is a dump of the tables and data needed to answer understand the system:-

The system consists of tutors and classes. The data in the table All_Tag_Relations stores tag relations for each tutor registered and each class created by a tutor. The tag relations are used for searching classes.

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`),
  KEY `tag_4` (`tag`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO `Tags` (`id_tag`, `tag`) VALUES
(1, 'Sandeepan'),
(2, 'Nath'),
(3, 'first'),
(4, 'class'),
(5, 'new'),
(6, 'Bob'),
(7, 'Cratchit');


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


INSERT INTO `All_Tag_Relations` (`id_tag`, `id_tutor`, `id_wc`) VALUES
(1, 1, NULL),
(2, 1, NULL),
(3, 1, 1),
(4, 1, 1),
(6, 2, NULL),
(7, 2, NULL),
(5, 2, 2),
(4, 2, 2),
(8, 1, 3),
(9, 1, 3);

Following is my query:-

This query searches for "first class" (tag for first = 3 and for class = 4, in Tags table) and returns all those classes such that both the terms first and class are present in the class name.

SELECT wtagrels.id_wc,SUM(DISTINCT( wtagrels.id_tag =3)) AS
       key_1_total_matches,
       SUM(DISTINCT( wtagrels.id_tag =4))                AS
       key_2_total_matches
FROM   all_tag_relations AS wtagrels
WHERE  ( wtagrels.id_tag =3
          OR wtagrels.id_tag =4 )
GROUP  BY wtagrels.id_wc
HAVING key_1_total_matches = 1
       AND key_2_total_matches = 1
LIMIT  0, 20  

And it returns the class with id_wc = 1.

But, I want the search to show all those classes such that all the search terms are present in the class name or its tutor name So that searching "Sandeepan class" (wtagrels.id_tag = 1,4) or "Sandeepan Nath" also returns the class with id_wc=1. And Searching. Searching "Bob First" should not return any classes.

Please modify the above query or suggest a new query, if possible using MyIsam - fulltext search, but somehow help me get the result.

Answer 1

I think this query would help you:

SET @tag1 = 1, @tag2 = 4; -- Setting some user variables to see where the ids go. (you can put the values in the query)

SELECT wtagrels.id_wc,
  SUM(DISTINCT( wtagrels.id_tag =@tag1 OR wtagrels.id_tutor =@tag1)) AS key_1_total_matches,
  SUM(DISTINCT( wtagrels.id_tag =@tag2 OR wtagrels.id_tutor =@tag2)) AS key_2_total_matches
FROM   all_tag_relations AS wtagrels
WHERE  ( wtagrels.id_tag =@tag1 OR wtagrels.id_tag =@tag2 )
GROUP  BY wtagrels.id_wc
HAVING key_1_total_matches = 1 AND key_2_total_matches = 1
LIMIT  0, 20

It returns id_wc = 1.
For (6, 3) the query returns nothing.