CODEWITHSUNDEEP
mysqlinner-joinalias
Esme_
Esme_

Reputation: 1520

Select from table based on values in another table in MySQL

I have two simple tables in a database

papers

ID Title                              Author 
1  A study of turtles                 Mary
2  Dietary habits of ducks            Kate
3  Similarities of turtles and cats   Fred

keywords

ID Keyword                            
1  turtles                 
2  ducks          
3  turtles
3  cats

I would like to select all the papers that have the keyword "turtles". That is, it would return

ID Title                              Author 
1  A study of turtles                 Mary
3  Similarities of turtles and cats   Fred

or 

ID Title                              Author   Keyword
1  A study of turtles                 Mary     turtles
3  Similarities of turtles and cats   Fred     turtles

I'm not concerned if the Keyword title is or is not included.

I think I need to use a select and inner join using an alias - but am not getting the syntax right.

I have tried

select * from (select papers.*, keywords.* from papers inner join keywords on papers.id = keywords.id) where keyword = "turtles";

which gives the error Every derived table must have its own alias

I've tried to follow What is the error "Every derived table must have its own alias" in MySQL? 

select * from (select papers.*, keywords.* from 
    (papers inner join keywords on papers.id = keywords.id) as T) 
    as T) 
    where keyword = "turtles";

which returns a syntax error.

I know there are many similar enquiries, but I am new to MySQL and am getting confused with the questions / examples.

EDIT: Clarifying that I am wanting to return the IDs that match the keyword "turtle" from the table keyword (1 and 3 in this example), then select from the papers table the rows corresponding to these IDs. I am not looking to find the keywords in the title.

Upvotes: 0

Views: 3191

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

We can try searching, for each paper title, the entire set of keywords, looking for a match. The REGEXP operator is helpful here. In particular, we are looking to match \bkeyword\b against anywhere in the paper title.

SELECT p.*
FROM papers p
WHERE EXISTS (SELECT 1 FROM keywords k
              WHERE p.title REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]'));

Demo

Edit:

If you just want to search the paper titles against a single known keyword, e.g. turtles, then use this simplification:

SELECT *
FROM papers
WHERE title REGEXP '[[:<:]]turtles[[:>:]]';

Upvotes: 1

kurkle
kurkle

Reputation: 383

Simple join (inner):

SELECT *
FROM keywords k
JOIN papers p USING (id)
WHERE k.keyword='turtles'

Upvotes: 1

Guy Louzon
Guy Louzon

Reputation: 1203

I really like Tim's answer

Here's my own attempt

SELECT
  *
 FROM
   papers p
   INNER JOIN keywords k ON p.`Title`  LIKE CONCAT('%', k.keyword ,'%')
  ;

SQLFiddle

As Tim commented on this answer, it'll bring substrings as well a full word match In order to resolve this, there a need to add spaces to the % parameters

SELECT
  *
 FROM
   papers p
   INNER JOIN keywords k ON p.`Title`  LIKE CONCAT('% ', k.keyword ,' %')
  ;

Upvotes: 0

Related Questions