Search code examples
mysqlinner-joinalias

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.

Solution

  • Simple join (inner):

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