3

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?

  • I'd put the three selects into a stored procedure and return any results in a temporary table, with a column designating which table they originated from. – LowlyDBA Feb 19 '15 at 17:32
  • I have no idea how to do this. I just tried putting the 3 selects in a stored procedure (the edit above), But unfortunately only the first select works when I do this. If you know how to solve this please provide me with an answer with some sample code :) I've been working on this all day and have to sleep now, Ill be at it again tomorrow. Hopefully someone here can help me – Green_qaue Feb 19 '15 at 17:58
  • What RDBMS are you using? SQL Server? MySQL? – LowlyDBA Feb 19 '15 at 18:05
  • Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) – Green_qaue Feb 19 '15 at 18:32
3

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
  • This look interesting, will try this tomorrow :) – Green_qaue Feb 20 '15 at 18:23
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.