LEFT JOIN of a string

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.

-------------Problems Reply------------

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

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

CASE count
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' ,*
FROM books
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.

Category:sql Views:0 Time:2010-08-10

Related post

  • What is the best way to String.Join a non-string array? 2010-07-06

    What is a shorthand way to String.Join a non-string array as in the second example? string[] names = { "Joe", "Roger", "John" }; Console.WriteLine("the names are {0}", String.Join(", ", names)); //ok decimal[] prices = { 39.99M, 29.99m, 29.99m, 19.99

  • Join list of string in python with string %s replacement 2010-09-29

    I have a list of ints that I want to join into a string ids = [1,2,3,4,5] that looks like 'Id = 1 or Id = 2 or Id = 3 or Id = 4 or ID = 5' I have an answer now but I thought it may be good to get the panel's opinion Edit: More information to the nay

  • Join list of string to comma separated and enclosed in single quotes 2011-08-03

    List<string> test = new List<string>(); test.Add("test's"); test.Add("test"); test.Add("test's more"); string s = string.Format("'{0}'", string.Join("','", test)); now the s is 'test's','test','test's more' but I need to replace the inner

  • How to join together some strings with conditions? 2011-10-14

    I have the next code: string result = string.Join(",", someCondition1 ? str1 : string.Empty, someCondition2 ? str2 : string.Empty, someCondition3 ? str3 : string.Empty, //some other conditions like those ...... ); If I have: someCondition1 = true; st

  • Join vector of strings to std::ostream (like boost::join) 2012-03-12

    I have a vector of strings and I want output it to stream (file stream, actually). And I want to have a delimiter between vector elements. There is a way to use standard ostream_iterator std::vector <std::string> strs; std::ostream_iterator<

  • MySQL - Find rows matching all rows from joined table AND string from other tables 2009-08-07

    this is a follow up from http://stackoverflow.com/questions/1242223/mysql-find-rows-matching-all-rows-from-joined-table Thanks to this site the query runs perfectly. But now i had to extend the query for a search for artist and track. This has lead m

  • How to join list of strings? 2010-03-11

    This is probably seriously easy to solve for most of you but I cannot solve this simply putting str() around it can I? I would like to convert this list: ['A','B','C'] into 'A B C'. Thanks in advance!! --------------Solutions------------- In [1]: L =

  • Joining every four strings in a list 2012-04-10

    I have a list like this: ['Ww','Aa','Bb','Cc','ww','AA','BB','CC'] And continuing in such a pattern, with varying capitals and lowercases. What I want to do is join every four items in this list together. So, the resulting new list (given the one abo

  • Algorithm for joining e.g. an array of strings 2008-09-12

    I have wondered for some time, what a nice, clean solution for joining an array of strings might look like. Example: I have ["Alpha", "Beta", "Gamma"] and want to join the strings into one, separated by commas – "Alpha, Beta, Gamma". Now I know that

  • Java equivalents of C# String.Format() and String.Join() 2008-10-09

    I know this is a bit of a newbie question, but are there equivalents to C#'s string operations in Java? Specifically, I'm talking about String.Format and String.Join. --------------Solutions------------- The Java String object has a format method (as

  • python string join performance 2009-01-24

    There are a lot of articles around the web concerning python performance, the first thing you read: concatenating strings should not be done using '+': avoid s1+s2+s3, instead use str.join I tried the following: concatenating two strings as part of a

  • Python join, why is it string.join(list) instead of list.join(string)? 2009-01-29

    This has always confused me. It seems like this would be nicer: my_list = ["Hello", "world"] print my_list.join("-") # Produce: "Hello-world" Than this: my_list = ["Hello", "world"] print "-".join(my_list) # Produce: "Hello-world" Is there a specific

  • Join a string using delimiters 2009-02-24

    What is the best way to join a list of strings into a combined delimited string. I'm mainly concerned about when to stop adding the delimiter. I'll use C# for my examples but I would like this to be language agnostic. EDIT: I have not used StringBuil

  • How to convert IEnumerable to string[] so I can use it with String.Join? 2009-11-05

    How can I convert the IEnumerable<char> "nonLetters" to a string[] so that I can use it with String.Join? string message = "This is a test message."; var nonLetters = message.Where(x => !Char.IsLetter(x)); Console.WriteLine("There are {0} no

  • Java: convert List to a join()d string 2009-11-17

    Javascript has Array.join() js>["Bill","Bob","Steve"].join(" and ") Bill and Bob and Steve Does Java have anything like this? I know I can cobble something up myself with StringBuilder: static public String join(List<String> list, String con

  • Write String.Join(Of T) in VB.Net 2010-08-18

    I have a simple code in C#: Console.WriteLine(string.Join<char>("", "")); And I can't convert it to VB.Net. Even reflector show me code in VB like: Console.WriteLine(String.Join(Of Char)("", "")) But it can't be compiled becouse I have an starg

  • string.Join on a List or other type 2010-08-31

    I want to turn an array or list of ints into a comma delimited string, like this: string myFunction(List<int> a) { return string.Join(",", a); } But string.Join only takes List<string> as the second parameter. What is the best way to do t

  • Entity Framework - how to join tables without LINQ and with only string? 2010-09-30

    I have a question about Entity Framework. Please answer if you know answer on this. I have such query : String queryRaw = "SELECT " + "p.ProductName AS ProductName " + "FROM ProductEntities.Products AS p " + "INNER JOIN CategoryEntities.Categories AS

  • Removing extra commas from string after using String.Join to convert array to string (C#) 2010-10-07

    Quick question here. I'm converting an array into a string using String.Join. A small issue I have is that, in the array some index positions will be blank. An example is below: array[1] = "Firstcolumn" array[3] = "Thirdcolumn" By using String.Join("

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.135 (s). 11 q(s)