Join two mysql tables with LIKE condition.

Question

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'

Answer 1

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%'

Answer 2

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';

Answer 3

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.