Can someone solve this 'flexible' WHERE Clause?

Answered

DSP User

• April 10, 2017 06:05

I have a table 'A' where each user has a flag for whether or not they want to see the count of the view 'B' with 0 records.

• If the flag is set to 1, the user should see all records from view 'B.’
• If the flag is set to 0, the user should only see the records where the Count > 0.

Here's simple example: Table 'A': Columns: UserID, ViewZeroRecords User1, 1 User2, 0 View 'B': Columns: ProjectID, boaUserID, Count 1, User1, 4300 2, User1, 200 3, User1, 0 1, User2, 4200 2, User2, 0 3, User2, 5000 User2 should not see the entry (2, User2, 0) in View B when the user looks at the page.

Any help is appreciated. Thank you Carole

1

• Best answer

  

  DSP Expert

  • April 11, 2017 07:03

  PTowe: If I understand the question correctly, it's just a simple where statement:

  SELECT B.ProjectID, B.boaUserID, B.[Count] FROM B INNER JOIN A ON B.boaUserID = A.UserID Where viewzeroRecords=1 or [Count]>0 This would also be a classic example of where you may want to use the Case statement in a where clause, like this: SELECT B.ProjectID, B.boaUserID, B.[Count] FROM B INNER JOIN A ON B.boaUserID = A.UserID where Case when viewzeroRecords=1 then 1 when [Count]>0 then 1 else 0 end =1

  This is not always the fastest solution as a case statement is a bit slow to process, but for complex where statements, it can be quicker than using lots of separate clauses separated by OR . By the way, its not considered good practice to call a column "Count." The use of key words in table names and column names is discouraged. I hope this helps

  1

Please sign in to leave a comment.