3

I'm wondering what is the most efficient way to obtain autocomplete results from multiple tables?

The caveat being; I want to be able to identify from what table the records are coming.

For example, given these example tables:

+- People
   +- id
   +- name
   +- age

+- Places
   +- id
   +- name
   +- distance

+- Things
   +- id
   +- name
   +- color

While containing some data arbitrary to this issue, the main focus here is the name columns (though these could be something else, or multiple per table being queried)

Anyways, I'm trying to figure out how to query each of these tables for data a' la autocomplete, and return the data and from what table it is. For example, the output for the letter A may look something like this:

Apple (Things)
Amy (People)
Aaron (People)
Anaheim (Places)
Axe (Things)

Would this be best accomplished with a single query, or multiple queries across the various tables?

Thanks in advance :)

Dan Lugg
  • 20,192
  • 19
  • 110
  • 174

1 Answers1

4

You could do it with at union, in a single query:

SELECT name, 'Things' as source FROM Things WHERE name LIKE 'A%'
UNION ALL
SELECT name, 'People' as source FROM People WHERE name LIKE 'A%'
UNION ALL
SELECT name, 'Places' as source FROM Places WHERE name LIKE 'A%'

Now you would get both the matching name and the source table, and you could format that nicely for your auto-complete drop down thingie.

Klaus Byskov Pedersen
  • 117,245
  • 29
  • 183
  • 222
  • 1
    Brilliant :) Thanks **Klaus Byskov Hoffmann**; I was leaning towards `UNION` however this implementation eluded me. (I'll accept in 3 minutes) – Dan Lugg Feb 19 '11 at 14:33
  • 1
    If you're dealing with many records, will the execution time of the query be acceptable with right indexing and result caching? – html_programmer Dec 14 '14 at 14:04
  • Will this be costlier than say having just one table consisting of all entities together and querying just that one table? – SexyBeast Jun 10 '16 at 22:25