CODEWITHSUNDEEP
mysqlsql
Mensur
Mensur

Reputation: 475

Join two mysql tables with LIKE condition.

I'm trying to run this code but i'm getting errors. Any ideas why? 

SELECT notes.project_id, notes.title, notes.description 
FROM notes, project_members 
WHERE notes.title LIKE '%second%' OR notes.description LIKE  '%second%'
WHERE notes.project_id = project_members.project_id AND project_members.user_id = '7'

Upvotes: 0

Views: 92

Answers (3)

Alok Patel
Alok Patel

Reputation: 8022

There should be single WHERE clause.

SELECT notes.project_id, notes.title, notes.description 
FROM notes, project_members 
WHERE 
(notes.title LIKE '%second%' OR notes.description LIKE  '%second%')
AND notes.project_id = project_members.project_id AND project_members.user_id = '7';

Query would look something above to AND between your LIKE conditions.

You may also consider using JOIN, Like this.

SELECT notes.project_id, notes.title, notes.description 
FROM notes
JOIN project_members
ON notes.project_id=project_members.project_id
WHERE 
    (notes.title LIKE '%second%' OR notes.description LIKE  '%second%')
AND project_members.user_id = '7';

Upvotes: 0

Cyclonecode
Cyclonecode

Reputation: 30001

Since you only can have one WHERE clause you could use a inner join like this:

SELECT notes.project_id, notes.title, notes.description 
  FROM project_members pm
INNER JOIN notes ON notes.project_id = pm.project_id AND pm.user_id = 7
  WHERE notes.title LIKE '%second%' OR notes.description LIKE '%second%'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You have two WHERE clauses, but single SELECT only allows one. But, this would easily be fixed if you used proper, explicit JOIN syntax:

SELECT n.project_id, n.title, n.description 
FROM notes n JOIN
     project_members  p
     ON n.project_id = p.project_id
WHERE (n.title LIKE '%second%' OR n.description LIKE '%second%') AND
      p.user_id = '7';

Note:

  • Always use proper, explicit JOIN syntax. Never use commas in the FROM clause.
  • Table aliases make the query easier to write and to read.
  • I'm pretty sure your WHERE conditions are missing parentheses, for your intended logic.
  • If user_id is a number of any sort, then don't use single quotes around "7". This can confuse both the compiler and people.

Upvotes: 0

Related Questions