Searching through joined tables only shows matches data from one table

Question

I'm trying to perform a search with the following query:

        SELECT 
            *
        FROM 
            question_index
        JOIN
            question_content
            ON question_index.id = question_content.question_id
        WHERE
            question_index.remove = '0' AND
            question_index.active = '1' AND         
            question_index.publish_date <= '$current_time' AND
            (
                question_index.question LIKE '%$keyword%' OR
                question_content.text LIKE '%$keyword%'
            )
        GROUP BY
            question_index.id
        ORDER BY 
            question_index.publish_date DESC

What I want to do is for the search to run through both tables and display results that match either "question_index.question" or "question_content.text"

But right now, it's only displaying the results that match "question_content.text". I tried removing "question_content.text LIKE '%$keyword%'" but not results appear at all. The only way I could get results that matches "question_index.question" is if I remove the join all together. But that's not the result I want.

since you have just `join`, you're doing an `inner` join by default, which means you get results where records exist on BOTH sides of the join. if you want all records from one side even if there's no matches on the other side, you need to use a `left join` or `right join`. — Marc B, Jun 28 '16 at 17:17
left join worked! please submit it as answer and I'll accept it, thx! — Joseph Mok, Jun 28 '16 at 17:27

Mahesh Madushanka · Answer 1 · 2016-06-28T17:42:24.130

0

  SELECT 
        *
    FROM 
        question_index LEFT
    JOIN
        question_content
        ON question_index.id = question_content.question_id
    WHERE
        question_index.remove = '0' AND
        question_index.active = '1' AND         
        question_index.publish_date <= '$current_time' AND
        (
            question_index.question LIKE '%$keyword%' OR
            question_content.text LIKE '%$keyword%'
        )
    GROUP BY
        question_index.id
    ORDER BY 
        question_index.publish_date DESC

edited Jun 28 '16 at 17:42

answered Jun 28 '16 at 17:24

Mahesh Madushanka

2,902
2
14
28

Union all will also work but updated it to Left join because from the performance point its better than union – Mahesh Madushanka Jun 28 '16 at 17:43

Searching through joined tables only shows matches data from one table

1 Answers1