0

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.

Esme_
  • 1,360
  • 3
  • 18
  • 30
  • Don't use alias same. do you already try change alias your table? – dwir182 Sep 17 '18 at 04:16
  • Sorry @dwir182 I'm not sure what you are asking. Can you please reword your question - I am very new to MySQL so apologies if I'm missing something obvious. – Esme_ Sep 17 '18 at 05:07

3 Answers3

1

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[[:>:]]';
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks Tim - I think my question though is more simplistic than this. I just want to find the IDs in the keyword table that match "turtles" (1 and 3), and then grab the entries from the papers table that have these IDs. I'm not looking to search for the words within the title (sorry for the confusion - I've been getting confused with a lot of the examples so wanted something I could easily tell if I did it correctly - hence having obvious keyword-title matches). I'll update the question to clarify this. – Esme_ Sep 17 '18 at 05:11
  • If you are manually feeding in the keyword to be searched directly in the query, then what role does the keywords table actually play here? – Tim Biegeleisen Sep 17 '18 at 05:13
  • It may not be the best way to do it (I really am new to all this) - but my intent was to have a database with a table containing the key information about a study (e.g. the papers table), and then a series of other tables containing the other data about the study (e.g. keywords table - but there will be others such as location, application area etc.). Each study may have multiple keywords, that won't necessarily be obvious from the information in the main table. I am then using different combinations of queries to generate layers in a map. – Esme_ Sep 17 '18 at 05:22
  • I have no idea what you are trying to do, and this did not come across in your question. Maybe you should tell us what the real problem is here. – Tim Biegeleisen Sep 17 '18 at 05:23
  • I'm not sure what is unclear - but I'll try explain again (although @kurkle's answer is working for me so this might be moot now). I want to find the IDs corresponding to keyword "turtles" from the table "keywords". I then want to return every row from the "papers" table that have the same ID. The example I gave is a toy problem - my real database will be more complicated, but this is the basic functionality I was looking for. Sorry for any confusion. – Esme_ Sep 17 '18 at 05:35
1

Simple join (inner):

SELECT *
FROM keywords k
JOIN papers p USING (id)
WHERE k.keyword='turtles'
kurkle
  • 353
  • 1
  • 2
  • 6
0

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 ,' %')
  ;
Guy Louzon
  • 1,175
  • 9
  • 19
  • This has the problem that it would match a keyword which happens to be a _substring_ of another word in the title. Also, it would return duplicates if a given paper happens to match more than one keyword. You can fix the second problem easily using `DISTINCT`. For the first one, you'll need to use `REGEXP`. – Tim Biegeleisen Sep 17 '18 at 04:36