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,
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.

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)

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.189 (s). 11 q(s)