简单的MySQL查询问题 - 获取相关记录，同时获取与之无关的记录

Question

Hey, I think I'm going about this the wrong way, but have tried many ways, none of which give me the desired results.

Basically I have one search field which is to check multiple tables. My issue is that when, say, an item doesn't have a related person, it won't return a result for that item. Other than that it is fine. I realize the problem is in the WHERE, demanding that it find only records that match in the other table, but I'm not sure how to rectify this.

Thanks!

$q = "SELECT DISTINCT item.*
            FROM item, item_people, people
            WHERE item.record_id = item_people.item_id AND people.record_id = item_people.people_id
            AND
            item.live = '1' 
            AND
            (concat_ws(' ',people.name_first,people.name) LIKE '%$search_param%' OR
            item.name_title LIKE '%$search_param%' OR
            item.city = '$search_param' OR 
            item.category LIKE '%$search_param%' OR
            item.on_lists LIKE '%$search_param%')
            $limit";

Answer 1

You would need an OUTER JOIN to return items without related people.

SELECT DISTINCT item.* /*But don't use *!
                        */
FROM item
    LEFT OUTER JOIN item_people
    ON  item.record_id = item_people.item_id
    LEFT OUTER JOIN people
    ON  people.record_id = item_people.people_id
WHERE item.live = '1'
AND
    (
        concat_ws(' ',people.name_first,people.name) LIKE '%$search_param%'
    OR  item.name_title LIKE '%$search_param%'
    OR  item.city = '$search_param'
    OR  item.category LIKE '%$search_param%'
    OR  item.on_lists LIKE '%$search_param%'
    )
    $limit