Possible to search multiple tables with a single query? [MSAccess/SQL Server]

Question

Hi all,

So my goal here is to have a single search field in an application that will be able to search multiple tables and return results.

For example, two of these tables are "performers" and "venues" and there are the following performers: "John Andrews","Andrew Smith","John Doe" and the following venues: "St. Andrew's Church","City Hall". Is there a way to somehow return the first two performers and the first venue for a search of "Andrew"?

My first thought was to somehow get all the tables aggregated into a single table with three columns; "SearchableText","ResultType","ResultID". The first column would contain whatever I want searched (e.g. Performer name), the second would say what is being shown (e.g. Performer) and the third would say the item's ID (note: all my tables have auto-incrementing primary keys for ease). The question for this idea is it possible to somehow do this dynamically or do I have to add code to have a table that automatically fills whenever a new row is updated/added/deleted from the performers and venues table (perhaps via trigger?).

My application is written in MSAccess (I know, I know, but I have no choice) on top of a SQL Server backend. I'd prefer this happen through MSAccess so I don't have to have a "searchme" table sitting on my SQL Server but any good result is acceptable :)

Answer 1

I think you are looking for the "union" sql keyword

Answer 2

I'd use full text indexing in SQL server, have a single table with your searchable text, and forign keys in your main tables that link to the search table. This way you can order your results by relevance.

Answer 3

I think you have a schema problem. Querying a UNION is almost always evidence of that (though not in all cases).

The question to me is:

What are you returning as your result?

If you find a person, are you displaying a list of people?

Or if you find a venue, a list of venues?

Or a mix of both?

I would say that if you want to return a list of both, then you'd want something like this:

  SELECT tblPerson.PersonID, tblPerson.LastName & ", " & tblPerson.FirstName, "Person"
  FROM tblPerson 
  WHERE tblPerson.LastName LIKE "Andrew*"
    OR tblPerson.FirstName  LIKE "Andrew*"

  UNION 

  SELECT tblVenue.Venue, tblVenue.Venue, "Venue" 
  FROM tblVenue
  WHERE tblVenue.Venue LIKE "Andrew*"
  ORDER BY Venue

This will give a list of the matches indicating which is a person and which a venue, and allow you to then select one of those and open a detail view (by checking the value in the third field).

What you definitely don't want to do is this:

  SELECT tblPerson.PersonID, tblPerson.LastName & ", " & tblPerson.FirstName, "Person"
  FROM tblPerson 

  UNION 

  SELECT tblVenue.Venue, tblVenue.Venue, "Venue" 
  FROM tblVenue

then saving that and trying to query it on the 2nd column. That will be extremely inefficient. You want your WHERE clause to be on fields that can be searched via the index, and that means each subquery of your UNION needs to have an appropriate WHERE clause.

--
David W. Fenton
David Fenton Associates