LEFT JOIN of a string

Question

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[0] AS Search_Term,* WHERE Title LIKE '%$s[0]%'  UNION 
SELECT $s[1] AS Search_Term,* WHERE Title LIKE '%$s[1]%' ...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.

Answer 1

I think this is what he wants:

--Results--

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

etc.

Maybe something like this:

SELECT
  CASE count
    count > 0
      'Search Term', books.title 'Title', books.author 'Author', books.call_no 'Call No'
    count <= 0
      'Search term 1'
  END CASE
FROM
  (SELECT COUNT('Search term 1') count, 'Search term 1' 'Search Term' ,*
   FROM books
   WHERE books.title LIKE 'Search term 1');

(you'll have to work the kinks out though)

Answer 2

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.

Answer 3

You could use Full Text Search (FTS) this way, if you concatenate the array values together:

   SELECT x.searchterm,
          b.*
     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.