On part of my site, users enter multiple search terms. Each of these search terms may return 0 rows, or return several. I perform a UNION on all the searches and get the rows.
Thing are much easier for me if I can get all the search terms in the response, regardless of whether they return any rows. Is there a way to essentially LEFT JOIN a string (in this case the search term) to a SELECT query?
More information update:
I have a table Books with the field Title.
The user enters up to 10 title search_terms, and I currently perform a UNION on all these to get matching rows. So it's like
SELECT $s AS Search_Term,* WHERE Title LIKE '%$s%' UNION SELECT $s AS Search_Term,* WHERE Title LIKE '%$s%' ...etc
I'd like it so that if there are no results for a given search, I still get the Search_Term back along with the NULL Title.
You could use Full Text Search (FTS) this way, if you concatenate the array values together:
FROM (SELECT $s[0 ... 20] AS search_term) x
LEFT JOIN BOOKS b ON MATCHES(b.title) AGAINST (x.searchterm)
$s[0 ... 20] is a placeholder for the search terms concatenated into a single string, separating each term with a space character.
The LEFT JOIN will ensure that you get your search term and NULL for the rest of the columns if nothing matches.
I think this is what he wants:
Search Term____Title________________________________Author______Call No
cats How to Care for Millions of Cats JohnB B.342
kittens Why Kittens are your only Friends Old Lady L.4239
rabies null null null
Maybe something like this:
count > 0
'Search Term', books.title 'Title', books.author 'Author', books.call_no 'Call No'
count <= 0
'Search term 1'
(SELECT COUNT('Search term 1') count, 'Search term 1' 'Search Term' ,*
WHERE books.title LIKE 'Search term 1');
(you'll have to work the kinks out though)
Join conditions work just like WHERE conditions, you can use "LIKE" or "=" to join on the condition of your choice. If you need more detail, I'll need some query examples and table definitions.