2

I am relatively new to the SQL programming, so please go easy on me.

I am currently writing a query, which would output the result based on the value from one of the outer parameters. The structure is currently looking like following:

@ShowEntireCategory bit = 0

select distinct
p.pk
p.name
--other columns
from dbo.Project P
--bunch of left joins
where p.Status = 'Open'
--other conditions

What I am trying to implement is: when the value of ShowEntireCategory is 1 (changed programmatically through radiobutton selection) it will show records of all subcategories, which are inside of the the category. When it is 0, it will only show records from the selected subcategory, while other subcategories in that category remains untouched.

I have been performing a research on the best approach, and it narrowed down to either WHERE statements or JOINs.

What I want to know is: which of these approaches I should use for my scenario? In my case the priority is optimization (minimum time to execute) and ease of implementation.

NOTE: My main goal here is not to receive a ready to use code here (though an example code snippets would be welcome), I just want to know a better approach, so I can continue researching in that direction.

Thank you in advance!

UPDATE

I have performed additional research on the database structure, and managed to narrow down to parameters relevant to the question

One is dbo.Project table, which contains: PK, CategoryKey (FK) (connected to the one in second table), Name, Description, and all other parameters which are irrelevant.

Second one is dbo.Area table, which contains: PK, AreaNumber, Name, CategoryKey (FK), IsCategory (1 = is category, 0 = not category).

Sorry, but I work in fast-paced environment, this is as much as I was able to squeeze. Please let me know if it is not enough.

Community
  • 1
  • 1
Vadzim Savenok
  • 930
  • 3
  • 14
  • 37
  • This would be somewhat dependent on how categories and sub-categories are stored in the database. – BrandonM Jul 26 '16 at 20:23
  • @BrandonM Can you please clarify? The only way I can answer right now is "4 subcategories within 1 category, with each subcategory having their own identifier". – Vadzim Savenok Jul 26 '16 at 20:25
  • Can you provide the structure of your tables and some sample data? Idealy as sql statements so we can set up an environment for tests – Philipp Jul 26 '16 at 20:30
  • Also in the code you have shown, nothing about categories or subcategories is mentioned, so the question as it is is not really clear – Philipp Jul 26 '16 at 20:32
  • @Philipp Now that I re-read it, it seems to be the case. Editing my post now. – Vadzim Savenok Jul 26 '16 at 20:33
  • Once you decide which DBMS you are using (mysql <> sql server) this would be a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Jul 26 '16 at 20:44

1 Answers1

0

With the information you provided the best solution would be to use a combination of WHERE clauses and JOINS. You would likely need to use a WHERE clause on the second table (described in the update) to select all rows which are categories. Then, you would JOIN this result with your other tables/data. Finally, you can use a CASE clause (details found here) to check your variable and determine if you need all categories or just some (which can be dealt with through an additional WHERE clause).

Not sure this entirely answers your question, but for a more detailed answer we would need a more detailed description of the database schema.

BrandonM
  • 390
  • 4
  • 12