1

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";
user438204
  • 13
  • 2

1 Answers1

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • You are absolutely correct. Thank you so much. What do you mean by "/*But don't use *! */" When I took the first asterisk away, the query failed. Thanks again! – user438204 Sep 02 '10 at 17:49
  • @user - Ah sorry for the confusion. It's better practice to list the column names explicitly rather than use `*`. **See: [Link to related question](http://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc)** – Martin Smith Sep 02 '10 at 18:03