Mysql getting data from tables with join

Question

I have created an sql query command to get data from these three tables , this command must help the user to search for a course from an search input , in this search input he can write the name of the course or the name of the teacher who posted the course orthe key words of the course... through: name of the course(cours table) ,through name of teacher(user table), through key words(motCle table

i have wrote this command , but i have i m getting a wrong result while querying with key words ,

SELECT *
FROM cours c
JOIN users u ON u.id= c.userId
WHERE (c.nom LIKE '%name%' OR u.nom LIKE '%name%') OR EXISTS(
SELECT *
FROM cours c
JOIN intermots i ON i.coursId= c.id
JOIN motcles m ON i.motCleId = m.id
WHERE m.nom LIKE '%name%')

i need to search data using , course name(cours table) or teacher name(user table) or keywords (motcle) — computer tricks
– computer tricks, Commented Mar 19, 2020 at 15:02
All the matching users will be joined with all courses, not just the matching courses. — Barmar
– Barmar, Commented Mar 19, 2020 at 15:03
Use separate queries for matching users and matching courses, then combine with UNION. — Barmar
– Barmar, Commented Mar 19, 2020 at 15:06
when i write a keyword he display all the courses in the table course ... — computer tricks
– computer tricks, Commented Mar 19, 2020 at 15:08

Thorsten Kettner · Accepted Answer · 2020-03-19 15:11:05Z

1

Your EXIST clause is not correlated. You want to know whether there exists key word for the row you are currently looking at. Remove the course table from the subquery and compare with the main query's course row instead.

SELECT *
FROM cours c
JOIN users u ON u.id= c.userId
WHERE c.nom LIKE '%name%' 
OR u.nom LIKE '%name%'
OR EXISTS
(
  SELECT *
  FROM intermots i
  JOIN motcles m ON i.motCleId = m.id
  WHERE i.coursId = c.id
  AND m.nom LIKE '%name%'
);

And here is an alternative with IN (where the subquery doesn't have to be correlated):

SELECT *
FROM cours c
JOIN users u ON u.id= c.userId
WHERE c.nom LIKE '%name%' 
OR u.nom LIKE '%name%'
OR c.id IN
(
  SELECT i.coursId
  FROM intermots i
  WHERE i.motCleId IN 
  (
    SELECT m.id
    FROM motcles m
    WHERE m.nom LIKE '%name%'
  )
);

answered Mar 19, 2020 at 15:11

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barmar · Accepted Answer · 2020-03-19 15:10:24Z

1

Combine separate queries for each match with UNION

-- Match course name
SELECT c.*
FROM cours AS c
WHERE c.nom LIKE '%name%'

UNION
-- Match teacher name
SELECT c.*
FROM cours AS c
JOIN users AS u ON c.userid = u.id
WHERE u.nom LIKE '%name%'

UNION
-- Match keywords
SELECT c.*
FROM cours AS c 
JOIN intermots i ON i.coursId= c.id
JOIN motcles m ON i.motCleId = m.id
WHERE m.nom LIKE '%name%'

answered Mar 19, 2020 at 15:10

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

Mysql getting data from tables with join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related