Databases
StackOverflow2013
Postsdbo
POMysql - Help me alter this query to apply AND logic instead of OR in searching?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql - Help me alter this query to apply AND logic instead of OR in searching?
    primarykey
    data
    text
    <p>First execute these tables and data dumps :-</p> <pre><code>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, 'key1'), (2, 'key2'); 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 (1, 1), (2, 1); </code></pre> <p>The following query finds all the tutors from Tutors_Tag_Relations table which have reference to at least one of the terms "key1" or "key2".</p> <pre><code>SELECT td . * FROM Tutors_Tag_Relations AS td INNER JOIN Tags AS t ON t.id_tag = td.id_tag WHERE t.tag LIKE "%key1%" OR t.tag LIKE "%key2%" Group by td.id_tutor LIMIT 10 </code></pre> <p>Please help me modify this query so that it returns all the tutors from Tutors_Tag_Relations table which have reference to both the terms "key1" and "key2" (AND logic instead of OR logic). Please suggest an optimized query considering huge number of data records (the query should NOT individually fetch two sets of tutors matching each keyword and then find the intersection).</p> <p><strong>Update</strong></p> <p>Taking the question to the next level. Please run the following fresh queries :-</p> <p>===================================================================================</p> <p>CREATE TABLE IF NOT EXISTS <code>learning_packs_tag_relations</code> ( <code>id_tag</code> int(10) unsigned NOT NULL DEFAULT '0', <code>id_tutor</code> int(10) DEFAULT NULL, <code>id_lp</code> int(10) unsigned DEFAULT NULL, KEY <code>Learning_Packs_Tag_Relations_FKIndex1</code> (<code>id_tag</code>), KEY <code>id_lp</code> (<code>id_lp</code>), KEY <code>id_tag</code> (<code>id_tag</code>) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;</p> <p>CREATE TABLE IF NOT EXISTS <code>learning_packs</code> ( <code>id_lp</code> int(10) unsigned NOT NULL AUTO_INCREMENT, <code>id_status</code> int(10) unsigned NOT NULL DEFAULT '2', <code>id_author</code> int(10) unsigned NOT NULL DEFAULT '0', <code>name</code> varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (<code>id_lp</code>) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;</p> <p>CREATE TABLE IF NOT EXISTS <code>tutors_tag_relations</code> ( <code>id_tag</code> int(10) unsigned NOT NULL DEFAULT '0', <code>id_tutor</code> int(10) DEFAULT NULL,</p> <p>KEY <code>Tutors_Tag_Relations</code> (<code>id_tag</code>), KEY <code>id_tutor</code> (<code>id_tutor</code>), KEY <code>id_tag</code> (<code>id_tag</code>) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;</p> <p>CREATE TABLE IF NOT EXISTS <code>users</code> ( <code>id_user</code> int(10) unsigned NOT NULL AUTO_INCREMENT, <code>name</code> varchar(100) NOT NULL DEFAULT '', <code>surname</code> varchar(155) NOT NULL DEFAULT '',</p> <p>PRIMARY KEY (<code>id_user</code>)</p> <p>) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;</p> <p>CREATE TABLE IF NOT EXISTS <code>tutor_details</code> ( <code>id_tutor</code> int(10) NOT NULL AUTO_INCREMENT, <code>id_user</code> int(10) NOT NULL, PRIMARY KEY (<code>id_tutor</code>) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ;</p> <p>CREATE TABLE IF NOT EXISTS <code>tags</code> ( <code>id_tag</code> int(10) unsigned NOT NULL AUTO_INCREMENT, <code>tag</code> varchar(255) DEFAULT NULL, PRIMARY KEY (<code>id_tag</code>), UNIQUE KEY <code>tag</code> (<code>tag</code>) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;</p> <p>ALTER TABLE <code>learning_packs_tag_relations</code> ADD CONSTRAINT <code>Learning_Packs_Tag_Relations_ibfk_1</code> FOREIGN KEY (<code>id_tag</code>) REFERENCES <code>tags</code> (<code>id_tag</code>) ON DELETE NO ACTION ON UPDATE NO ACTION;</p> <p>ALTER TABLE <code>learning_packs</code></p> <p>ADD CONSTRAINT <code>Learning_Packs_ibfk_2</code> FOREIGN KEY (<code>id_author</code>) REFERENCES <code>users</code> (<code>id_user</code>) ON DELETE NO ACTION ON UPDATE NO ACTION;</p> <p>ALTER TABLE <code>tutors_tag_relations</code> ADD CONSTRAINT <code>Tutors_Tag_Relations_ibfk_1</code> FOREIGN KEY (<code>id_tag</code>) REFERENCES <code>tags</code> (<code>id_tag</code>) ON DELETE NO ACTION ON UPDATE NO ACTION;</p> <p>INSERT INTO <code>test</code>.<code>users</code> ( <code>id_user</code> , <code>name</code> , <code>surname</code> ) VALUES ( NULL , 'Vivian', 'Richards' ), ( NULL , 'Sachin', 'Tendulkar' );</p> <p>INSERT INTO <code>test</code>.<code>users</code> ( <code>id_user</code> , <code>name</code> , <code>surname</code> ) VALUES ( NULL , 'Don', 'Bradman' );</p> <p>INSERT INTO <code>test</code>.<code>tutor_details</code> ( <code>id_tutor</code> , <code>id_user</code> ) VALUES ( NULL , '52' ), ( NULL , '53' );</p> <p>INSERT INTO <code>test</code>.<code>tutor_details</code> ( <code>id_tutor</code> , <code>id_user</code> ) VALUES ( NULL , '54' );</p> <p>INSERT INTO <code>test</code>.<code>tags</code> ( <code>id_tag</code> , <code>tag</code> ) VALUES ( 1 , 'Vivian' ), ( 2 , 'Richards' );</p> <p>INSERT INTO <code>test</code>.<code>tags</code> (<code>id_tag</code>, <code>tag</code>) VALUES (3, 'Sachin'), (4, 'Tendulkar'); INSERT INTO <code>test</code>.<code>tags</code> (<code>id_tag</code>, <code>tag</code>) VALUES (5, 'Don'), (6, 'Bradman');</p> <p>INSERT INTO <code>test</code>.<code>learning_packs</code> (<code>id_lp</code>, <code>id_status</code>, <code>id_author</code>, <code>name</code>) VALUES ('1', '1', '52', 'Cricket 1'), ('2', '2', '52', 'Cricket 2');</p> <p>INSERT INTO <code>test</code>.<code>tags</code> (<code>id_tag</code>, <code>tag</code>) VALUES ('7', 'Cricket'), ('8', '1'); INSERT INTO <code>test</code>.<code>tags</code> (<code>id_tag</code>, <code>tag</code>) VALUES ('9', '2');</p> <p>INSERT INTO <code>test</code>.<code>learning_packs_tag_relations</code> (<code>id_tag</code>, <code>id_tutor</code>, <code>id_lp</code>) VALUES ('7', '52', '1'), ('8', '52', '1'); INSERT INTO <code>test</code>.<code>learning_packs_tag_relations</code> (<code>id_tag</code>, <code>id_tutor</code>, <code>id_lp</code>) VALUES ('7', '52', '2'), ('9', '52', '2');</p> <p>===================================================================================</p> <p>About the new system - - The system now has 4 more tables - tutors, Users (linked to tutor_details), learning_packs, learning_packs_tag_relations - Tutors create packs - tag relations for tutors stored in tutors_tag_relations and those for packs stored in learning_packs_tag_relations.</p> <p>Now I want to search learning_packs, with the same AND logic. Help me modify the following query so that searching pack name or tutor's name, surname results all active packs (either directly those packs or packs created by those tutors).</p> <p>==================================================================================</p> <p>select lp.* </p> <p>from Learning_Packs AS lp </p> <p>LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON lp.id_lp = lptagrels.id_lp </p> <p>LEFT JOIN Tutors_Tag_Relations as ttagrels ON lp.id_author = ttagrels.id_tutor LEFT JOIN Tutor_Details AS td ON ttagrels.id_tutor = td.id_tutor LEFT JOIN Users as u on td.id_user = u.id_user</p> <p>JOIN Tags as t on (t.id_tag = lptagrels.id_tag) or (t.id_tag = ttagrels.id_tag)</p> <p>where lp.id_status = 1 AND ( t.tag LIKE "%Vivian%" OR t.tag LIKE "%Richards%" )</p> <h1>group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20</h1> <p>As you can see, searching "Cricket 1" returns that pack but searching Vivian Richards does not return the same pack.</p> <p>Please help</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSandeepan Nath
    1. USSandeepan Nath
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMysql - Help me alter this query to apply AND logic instead of OR in searching?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMysql - Help me alter this query to apply AND logic instead of OR in searching?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis looks like homework...
      singulars
      1. POMysql - Help me alter this query to apply AND logic instead of OR in searching?
      1. UScjk
    2. COAre you sure you need to use LIKE here? Perhaps you could just check for exact matches? This would work for your example data.
      singulars
      1. POMysql - Help me alter this query to apply AND logic instead of OR in searching?
      1. USMark Byers
    3. COHey Mark, did you remove your reply? I am actually new to stackoverflow. I was wrong in my earlier reply, the like is needed to return partial matches. I can apply full text search to optimize later. But now can anyone help me apply the AND logic?
      singulars
      1. POMysql - Help me alter this query to apply AND logic instead of OR in searching?
      1. USSandeepan Nath
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload