search in multiple fields 1

[mufka]

I need help creating a query that pulls records based on a search string. The catch is that I'd like to search for the string in multiple fields (e.g. item_description and item_title) and return one entry (for each hit) if it is found in one or the other (no duplicates if it is found in both). I think I could get this to work with MATCH AGAINST, but I need to use LIKE and I don't think that would work.

Is there another way to do this?

 

[r937]

SELECT item_description
     , item_title
     , item_other_columns
  FROM items
 WHERE item_description LIKE '%searchstring%'
    OR item_title LIKE '%searchstring%'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mufka]

I apologize for a mistake in my first post. I am pulling records from two tables. The item_description, item_title and a third field, item_container are in one table but the container detail is in another table.

My original query selected all records:

SELECT 
     c.container_id, 
     c.container_description, 
     c.container_location, 
     i.item_id, 
     i.item_description,
     i.item_title 
  FROM 
     containers c, 
     items i 
  WHERE 
     c.container_id=i.item_container 
  ORDER BY 
     i.item_id

Since I have already joined the tables with the WHERE on the container_id, I don't think I can use OR in the search query.

 

[r937]

well, yes, you could've, but it's better if you use JOIN syntax, then the AND/OR complexity in the WHERE clause is avoided

SELECT c.container_id
     , c.container_description
     , c.container_location
     , i.item_id
     , i.item_description
     , i.item_title   
  FROM items AS i
INNER
  JOIN containers AS c
    ON c.container_id = i.item_container
 WHERE item_description LIKE '%searchstring%'
    OR item_title LIKE '%searchstring%'
ORDER 
    BY i.item_id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon