Searching multiple tables in SQL

Question

This question will involve some C# code but I hope that is fine, in the end it is the SQL that is the issue. (To see short question look at end of post)

So I have a site, where you can add a site in to a category as well as a subcategory. Now I want to search the entire database using the same searchbox, redirecting to the same page.If the search finds a category I want the page to display a list of sites in that category. And if I search for a site I want to show a profile of that site.

So what does this mean? Well, it means I need a way to tell if the result (from the search) is coming from my site-table, my category-table or my subcategory-table.

So far, I have the following SQL:

"SELECT url, 'Web' As Type FROM Website WHERE (url=@search)
UNION ALL
SELECT category, 'Cat' As Type FROM Category WHERE (category=@search)";

Now this does search both tables, and return a type of "Web" if it comes from the Website-table or a type of "Cat" if it comes from the category table. However, the result always is under column "url", even if the result comes from the category-table.

C# that works:

using (SqlDataReader reader = cmd.ExecuteReader())
{
   if (reader.Read())
   {
       if (reader.GetString(reader.GetOrdinal("Type")) == "Web")
       {
           Label1.Text = reader.GetString(reader.GetOrdinal("url"));
       }
       else if (reader.GetString(reader.GetOrdinal("Type")) == "Cat")
       {
          Label1.Text = reader.GetString(reader.GetOrdinal("url"));
        }
    }

C# that does not work:

 using (SqlDataReader reader = cmd.ExecuteReader())
{
   if (reader.Read())
   {
       if (reader.GetString(reader.GetOrdinal("Type")) == "Web")
       {
           Label1.Text = reader.GetString(reader.GetOrdinal("url"));
       }
       else if (reader.GetString(reader.GetOrdinal("Type")) == "Cat")
       {
          Label1.Text = reader.GetString(reader.GetOrdinal("category"));
        }
    }

As above, you can see I only get the Type when I add "url" to the reader. Which I understand, in a UNION ALL it selects the first column as the name.

What I need is some way to do this so that I can select ALL (*) from my tables with one query and then be able to know what table it came from. Something like this (pseudocode):

if(search is from Website-table){
DisplayWebsitet();
}
else if(search is from Category-table){
DisplayCategory();
}
else{
DisplaySubCategory();
}

Is there a way to do this using SQL? Is there for example some if/else function I could use? There should be since at for example www.imdb.com you can search for both actor and movie. Or do I need to run 3 different queries? something I really want to avoid doing.

EDIT: Here is something that kind of gives a result I want but only works inside SQL-studio. I cant use this in C# for some reason. Maybe if I made it a stored procedure and loaded it from C#?

Scenario - If someone searches for "www.facebook.com":

SELECT Category.*, 'Cat' As Type FROM Category WHERE category='www.facebook.com'

SELECT SubCategory.*, 'Sub' As Type FROM SubCategory WHERE subCategory='www.facebook.com'

SELECT Website.*, 'web' As Type FROM Website WHERE url='www.facebook.com'

This return 2 empty results and 1 result (from website table) with all the corresponding information to "www.facebook.com". If I save this as a stored procedure, can I access the type from C#? Is it a good way to do this?

Answer 1

A quick way to resolve your issue would be simply to return a Null column in each of the select statements.

SELECT 
    url, NULL AS Category, NULL AS SubCategory,'Web' As Type FROM Website WHERE (url='www.facebook.com')
UNION ALL
SELECT 
     NULL, category, NULL AS SubCategory, 'Cat' As Type FROM Category WHERE (category='www.facebook.com')
UNION ALL
SELECT
    NULL, NULL, subCategory, 'Sub' As Type FROM SubCategory WHERE (subCategory='www.facebook.com')

This generate the following output which should match your requirement of output

       url       | Category | SubCategory  | Type
www.facebook.com |  NULL    |    NULL      |  Web

Answer 2

I don't know which data access technology you are using, but certainly in plain ADO.NET it's definitely a supported feature to have queries that return multiple resultsets.

For example, if you have a SqlDataReader, you step through the resultsets with NextResult.

It would then be a straightforward matter to read each of the three resultsets in turn and see which one had records in it.