Clever Geek Handbook

Can I search multiple tables with one query? [MSAccess / SQL Server]

So my goal is to have one search field in the application that can search multiple tables and return results.

For example, two of these tables are "performers" and "locations" and the following performers: "John Andrews", "Andrew Smith", "John Doe" and the following locations: "St. Andrew's Church", "Town Hall". Is there a way to somehow get back the first two artists and the first place to search for "Andrew"?

My first thought was to somehow collect all the tables concatenated into a single table with three columns; SearchableText, ResultType, ResultID. The first column will contain whatever I want to search for (eg artist name), the second will say what is displayed (eg Artist), and the third will indicate the item ID (note: all my tables have auto incrementing primary keys for convenience) The question in this idea is whether it is possible to somehow do this dynamically or do I need to add code to have a table that is auto-populated whenever a new row is updated / added / removed from the performers and venues table (perhaps via a trigger? ).

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

0


source to share


3 answers


I think you are looking for the sql keyword



+2


source


I would use full text indexing on SQL server, have one table with text to look up and forign keys in your main tables that reference the lookup table. This way, you can order your results by relevance.



+1


source


I think you have a schematic problem. A UNION query is almost always proof of this (although not in all cases).

Question for me:

What are you returning as a result?

If you find a person, do you show a list of people?

Or, if you find a meeting point, a list of places?

Or a combination of both?

I would say that if you want to return a list of both, then you need 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 matches that indicate who the person and venue is, and then allows you to select one and open the 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 save that and try to query it in the second column. This will be extremely ineffective. You want your WHERE clause to be in fields that can be searched by index, which means that every subquery of your UNION must have a matching WHERE clause.

0


source






More articles:


All Articles