Select from table based on values in another table in MySQL

sql update column with value from another table
create table from select mysql
mysql insert into select
how to use select and update together in mysql
insert into sql
mysql create table
update select mysql
mysql join

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.

Simple join (inner):

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

How to select data from a table which matches some rows in another , How do I select data from a table which matches some rows in another table in MySQL? What are you trying match the rows by? Are you trying to match it based​  Search for: How to select columns from different tables in mysql. How to select columns from different tables in mysql

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[[:>:]]';

13.1.20.4 CREATE TABLE SELECT Statement, You can create one table from another by adding a SELECT statement at the For each row in table foo , a row is inserted in bar with the values from foo REPLACE SELECT statements are flagged as unsafe for statement-based replication. Select value from mysql table when field equal to many values. Tag: mysql. I have simple mysql table with 2 fields: atr_id | atr_val_id 1 | 100 1 | 200 1 | 300 2

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 ,' %')
  ;

MySQL INSERT INTO SELECT Explained By Practical Examples, how to use the MySQL INSERT INTO SELECT statement to insert data into a table other tables to a table or to summary data from multiple tables into a table​. I have stumped on this as I am a total beginner in MySql. Here is a the basic of how the two tables are formed. Table 1 id,product_id, product_name. Table 2 id,product_id,active. Now i know how to do a select statement to query the results from one table but when I have to involve two, I am lost.

SQL Server: UPDATE Statement, This SQL Server tutorial explains how to use the UPDATE statement in SQL Server There are 3 syntaxes for the UPDATE statement depending on whether you are UPDATE table1 SET column1 = (SELECT expression1 FROM table2 WHERE that shows how to update a table with data from another table in MySQL. select A.* from table_A A where A.id in ( select B.id from table_B B where B.tag = 'chair' ) Alternatively you could join the tables and filter the rows you want: select A.* from table_A A inner join table_B B on A.id = B.id where B.tag = 'chair'. You should profile both and see which is faster on your dataset.

Updating a MySQL table with values from another table?, We can update another table with the help of inner join. mysql> INSERT into tblFirst values(1,'John'); Query OK, 1 row affected (0.17 sec) mysql> INSERT into tblFirst values(2,'Bob'); Query mysql> SELECT * from tblFirst;. Look at the above output, the last name is matching from the first table record. Look at the sample output. The id is updated, which was 4 but now it is 1.

update columns values with column of another table based on , You can try the below code: UPDATE table1. SET table1.Price = table2.price. FROM table1 INNER JOIN table2 ON table1.id = table2.id. SELECT statement provides an easy way to insert rows into a table from another table. If you want to copy data from one table to another in the same database, use INSERT INTO SELECT statement in MySQL. It’s a very quick process to copy large amount data from a table and insert into the another table in same MySQL database.

Comments
  • Don't use alias same. do you already try change alias your table?
  • 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.
  • No relation exists between the id columns from the two tables.
  • @TimBiegeleisen are you sure? Keywords table contains 2 rows for id 3. Also in original question the relationship has been used in both queries.
  • It was intentional - the id is the connecting factor between the different records
  • 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.
  • 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?
  • 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.
  • 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.
  • 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.
  • 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.