left outer join issue in mysql

Question

I am try to get all the rows in parts_keywords not included in parts_questions. Unfortunately, I am getting all the rows of both tables.

SELECT *FROM parts_keywords 
    LEFT OUTER JOIN parts_questions ON parts_keywords.ID = parts_questions.question_id
     WHERE parts_questions.question_id IS NULL
     AND parts_keywords.term  LIKE '%where%' || parts_keywords.term LIKE '%how%' || parts_keywords.term LIKE '%who%' || parts_keywords.term LIKE '%what%' 
    ORDER BY parts_keywords.ID DESC lIMIT 15

I have tried using the OR instead of ||. I have also tried using MATCH instead of LIKE to no avail.

When i use only one LIKE element, such as 'who' i get the correct results. However, it is important for me to get result for all LIKE elements together.

Please help

Try adding parentheses to your WHERE clause. It's not grouping the way you expect. — Barmar, Feb 15 '13 at 22:01

score 0 · Answer 1 · answered Feb 15 '13 at 22:01

you're missing () around your OR clauses

SELECT *
FROM parts_keywords 
  LEFT OUTER JOIN parts_questions ON parts_keywords.ID = parts_questions.question_id
WHERE parts_questions.question_id IS NULL
AND (parts_keywords.term  LIKE '%where%' 
 OR parts_keywords.term LIKE '%how%'
 OR parts_keywords.term LIKE '%who%'
 OR parts_keywords.term LIKE '%what%')
ORDER BY parts_keywords.ID DESC lIMIT 15

score 0 · Accepted Answer · answered Feb 15 '13 at 22:06

0

Try using paranthesis

SELECT * FROM parts_keywords pk
LEFT OUTER JOIN parts_questions pq ON pk.ID = pq.question_id
WHERE pq.question_id IS NULL
AND ( pk.term LIKE '%where%' OR pk.term LIKE '%how%' OR pk.term LIKE '%who%' OR pk.term     LIKE '%what%' )
ORDER BY parts_keywords.ID DESC lIMIT 15

answered Feb 15 '13 at 22:06

ilikemypizza

362
2
13

lol - love how this is the same answer as mine only posted 15 minutes later. Nicely played. – Matt Busche Feb 15 '13 at 23:44
My apologies, when I originally posted my answer there were no other responses to the question – ilikemypizza Feb 18 '13 at 16:51

left outer join issue in mysql

2 Answers2