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.

Improve this question
6
  • This would be somewhat dependent on how categories and sub-categories are stored in the database. Commented Jul 26, 2016 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". Commented Jul 26, 2016 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 Commented Jul 26, 2016 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 Commented Jul 26, 2016 at 20:32
  • @Philipp Now that I re-read it, it seems to be the case. Editing my post now. Commented Jul 26, 2016 at 20:33

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Sorry if that was not detailed enough. But I will accept your answer and perform additional research.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.