Searching multiple tables in SQL

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

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

StackExchange Database Administrators Q#93322, answer score: 3