I have three tables and I have trouble figure out how to make the mysql statement to get the correct records. What i want to do is:

1. find if the query-keyword is in keywords table and get the id
2. check connection table for keywordid and get all adid's
3. fetch all records from ads table that have the connection and is status 1

What also want to do is to match e.g "travel" to "travels", "travel to europe" et.c. (Like a phrase match)

Ads table:

keywords table:

connection table:

CodePudding user response：

as per your requirement please try below Query

SELECT * FROM keywords AS t1
   INNER JOIN CONNECTION AS t2 ON (t1.id=t2.keywid)
   INNER JOIN Ads AS t3 ON (t2.adid=t3.id)
   WHERE t1.keyword like '%here your keyword%' AND t3.status=1;

CodePudding user response：

According to the information given the query needed is as follows.

create table ads (
id    int,
title varchar(10),
descr varchar(10),
status    varchar(10));
insert into ads values
(0,   'title1',   'desc1',    1),
(1,   'title2',   'desc2',    0),
(2,   'title3',   'desc3',    1);
create table keywords (
id    int,
keyword varchar(10));
insert into keywords values
(0,   'keyw1'),
(1,   'keyw2'),
(2,   'keyw3');
create table connection (
id    int,
keywid    varchar(10),
adid int);
insert into connection values
(0    ,'keyid1',  2),
(1    ,'keyid2',  1),
(2    ,'keyid3',  3);
GO

9 rows affected

SELECT a.*
FROM keywords k
JOIN connection c
ON k.id = c.id
JOIN ads a
ON c.adid = a.id
WHERE k.keyword LIKE '%keyw%'
AND status = 1;
GO

id | title  | descr | status
-: | :----- | :---- | :-----
 2 | title3 | desc3 | 1     

db<>fiddle here