1

I want to implement like search for autocomplete.I have two tables,Location and SubLocation.

I want to return only one field for this like.Here is my query

SELECT l.loc_name,sl.sub_loc FROM Location l,SubLocation sl 
 where l.loc_name LIKE '$term%' OR sl.sub_loc='$term%'

I want to show matching result from both tables as one return.EG,if i type D so i can view Dubai from first table and DubaiMarina from second table as one coloumn

MPelletier
  • 16,256
  • 15
  • 86
  • 137

1 Answers1

1

You can use UNION

SELECT l.loc_name FROM Location l
 where l.loc_name LIKE '$term%' 
UNION
SELECT sl.sub_loc FROM SubLocation sl 
 where sl.sub_loc='$term%'

If the tables do not have duplicates, you can replace UNION with UNION ALL as the union all option will not look for duplicates, it might be a little faster depending on the amount of data in returned by the queries.

Praveen Lobo
  • 6,956
  • 2
  • 28
  • 40
  • UNION is too expensive and time taken to search too many records –  Sep 08 '13 at 18:00
  • @Methew: You can also use [Apache Solr](http://lucene.apache.org/solr/) if this is slow for you. – P̲̳x͓L̳ Sep 08 '13 at 18:09
  • 1
    How about using `UNION ALL`? – zedfoxus Sep 08 '13 at 18:10
  • @Methew could you please how Union is expensive? Are you missing any information in the question? – Praveen Lobo Sep 08 '13 at 18:11
  • Depends on the options you have. if you have more than one query giving you the same result, then you can look at performance. – Praveen Lobo Sep 08 '13 at 18:14
  • `UNION` will combine recordsets and then eliminate duplicates. If the OP is union-ing thousands of duplicate records, it will be more expensive than `UNION ALL`. Union all will combine records and return it without eliminating duplicates. – zedfoxus Sep 08 '13 at 18:24