Searching Unrelated Database Tables

Most data driven sites include a search option, where a user is able to search a database table or a related table for a specific value. An example of this is a site like Amazon.com, where you might want to search on a book subject, or a book author. Most likely, the author name is stored in a separate table. You can easily search on title, author, subject, or any of the above by writing a SQL statement that joins the related data.

What if you want the user to be able to search multiple tables that do not relate, however? This article will address joining unrelated tables for a database search. The article assumes that you have a basic understanding of SQL syntax.

Why would you need to do this? Several examples spring to mind. We'll look at each one of these situations:

A site like Community MX, where we have articles AND news items. The article information is stored separately from the news items. However, a search of the site will show results from both.
Two related sites that contain similar content, but reside in different tables or different databases. This could occur in various situations, such as if you host two similar sites, or if you have a client who has things in two separate databases.
An e-store that contains a product list but also contains a blog.

These are just some of the situations where you might need to search across unrelated tables. The key to doing unrelated searches is the UNION statement in SQL. Here is an example of how it works:

SELECT * FROM myTable1

UNION

SELECT * FROM myTable2

In that statement, the SELECT statements for the two unrelated tables are simply merged together. It's the equivalent of writing a SELECT statement, and then appending the result of a second SELECT statement to the end of the first. That's pretty simple, but the above statement assumes your field names in both tables are the same. This is usually not the case.

Let's look at the following two tables as an example, using the first situation described--searching across article descriptions and news items:

CREATE TABLE dbo.MyNews (
  NewsID int IDENTITY (1, 1) NOT NULL ,
  NewsItem varchar (255) NULL
)

CREATE TABLE dbo.MyArticles (
  ArticleID int IDENTITY (1, 1) NOT NULL ,
  ArticleText varchar (2000) NULL ,
  ArticleCategory varchar (50) NULL
)

In these tables, the fields are named differently, and there are an uneven number of fields in the tables. To create a basic UNION here, we'll use a field alias. To use an alias, simply give the field an arbitrary name using the AS keyword.

The AS keyword is optional in most databases. You can say SELECT NewsID MyID FROM MyNews and it will work the same as SELECT NewsID AS MyID FROM MyNews.

This allows us to "rename" the fields in each table to create a resultset. For now, we'll ignore the ArticleCategory field and use the two "similar" fields from each table:

SELECT NewsID AS MyID
, NewsItem AS MyItem
FROM MyNews

UNION

SELECT ArticleID AS MyID
, ArticleText AS MyItem
FROM MyArticles

That is pretty simple, and now you can search across both tables. To add the ArticleCategory field from the MyArticles table, you need to simply create an alias in the query to the MyNews table and use a hard-coded value for the field value where no value exists in the MyNews table:

SELECT NewsID AS MyID
, NewsItem AS MyItem
, 'News' AS Category
FROM MyNews

UNION

SELECT ArticleID AS MyID
, ArticleText AS MyItem
, ArticleCategory AS Category
FROM MyArticles

Now, the correct article categories will be displayed from the MyArticles table, and the word "News" will be displayed for any item from the MyNews table.

The easiest way to use a query like this is as a view in your database. If you were to save the SQL statement above as MySearchView, you could then search the view from your web page rather than search the individual tables. You could write a SQL statement in the Dreamweaver advanced recordset dialog box like this:

SELECT * FROM MySearchView
WHERE MyItem LIKE '%searchword%'

The variable searchword would be set up in Dreamweaver as an incoming form variable. The results would be the same as if you had placed the SQL statement with the UNION clause into your web page, with the added bonus that your web page code is more easily readable.

The second situation described a search of two unrelated tables in completely different databases. This situation might occur if you are searching content from two different e-commerce sites that reside on the same server. Here is an example, using Pubs and Northwind from SQL Server:

SELECT Title AS MySearchField
FROM pubs.dbo.titles

UNION

SELECT ProductName as MySearchField
FROM Northwind.dbo.Products

Finally, the third situation is similar to the first--a blog description being searched along with a product name and product description from a different table:

SELECT BlogItem AS Item
, '' AS Description
FROM Blog
UNION
SELECT ProductName AS Item
, ProductDescription AS Description
FROM Products

Each of these situations is similar in that different tables had to be joined that had absolutely NO relation to each other. Still, we were able to create a virtual table that consisted of all rows of both tables. This can be expanded further to include more than two tables just as easily.

One caveat to using this method is that if you want to use an ORDER BY clause, it has to be the last item in the SQL statement--your ordering will occur across all rows of all tables. In other words, the "virtual" table will be sorted rather than having each table sorted individually and the results of the second query appended to the first. Using our first example, the ORDER BY clause would be at the end:

SELECT NewsID AS MyID
, NewsItem AS MyItem
, 'News' AS Category
FROM MyNews

UNION

SELECT ArticleID AS MyID
, ArticleText AS MyItem
, ArticleCategory AS Category
FROM MyArticles

ORDER BY ArticleCategory

To get your results ordered within the table rather than as an overal result, you could do something like this:

SELECT NewsID AS MyID
, NewsItem AS MyItem
, 'News' AS Category
, 'a' AS TableOrder
FROM MyNews

UNION

SELECT ArticleID AS MyID
, ArticleText AS MyItem
, ArticleCategory AS Category
, 'b' AS TableOrder
FROM MyArticles

ORDER BY TableOrder, Category

In this example, we have created a virtual field named TableOrder and simply given it a hard-coded value of 'a' or 'b'.

Note: If you are creating the UNION query as a view, you have no need for an ORDER BY clause. Put the ORDER BY clause into your SELECT statement that retreives the data from the view.

The techniques shown should work with any database that supports the UNION keyword. UNION was implemented in MySQL as of version 4.0.