Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 143
  • Last Modified:

Simplify T-SQL query

Hi  experts,

I have inherited this query and cannot work it out, especially the WHERE clause.  Could someone put it into English please?

What part does the OR operator play, ie. what criteria are on either side of it?  The parentheses are confusing me!  I am not sure they are configured correctly.

SELECT *
FROM MY_TABLE AS F
INNER JOIN OTHER_TABLE AS W ON F.PKEY = W.PKEY

WHERE F.PROJECT = 'ABC'
  AND F.SITE_ID = 'XYZ'
  AND F.FRACTION = 'QQQ'
  AND W.WASH_TYPE = 'GGG'
  AND (
         (
            (Upper
               ('?Do you wish to use a site list?') = 'NO'
             AND F.PROJECT = 'ABC') OR EXISTS
               (SELECT F.SITE_ID From GB_SITE_LIST AS L
                WHERE L.SITE_ID = F.SITE_ID
                  AND L.SITE_LIST_NAME = 'ZZZ'
                  AND L.PROJECT = 'ABC'
               )
         )
      )
0
colinspurs
Asked:
colinspurs
  • 5
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
For a row to be selected, all the previous WHERE conditions must be true, of course and then:

(Upper
               ('?Do you wish to use a site list?') = 'NO'
             AND F.PROJECT = 'ABC') OR EXISTS
               (SELECT F.SITE_ID From GB_SITE_LIST AS L
                WHERE L.SITE_ID = F.SITE_ID
                  AND L.SITE_LIST_NAME = 'ZZZ'
                  AND L.PROJECT = 'ABC'
               )


Either:
(1) The '?Do you ...' must be 'NO'
      AND
      F.PROJECT must be 'ABC' -- but that was in an earlier condition anyway
OR
(2)  the GB_SITE_LIST table must have a row that matches on SITE_ID, SITE_LIST_NAME and PROJECT
must also be true.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there are indeed "too many" brackets, but don't get confused ...
re-tabbing etc the code slightly makes it easier:
AND ((
            (    Upper('?Do you wish to use a site list?') = 'NO'  AND F.PROJECT = 'ABC'   ) 
     OR EXISTS (SELECT F.SITE_ID From GB_SITE_LIST AS L
                             WHERE L.SITE_ID = F.SITE_ID
                               AND L.SITE_LIST_NAME = 'ZZZ'
                                AND L.PROJECT = 'ABC'
                       )
         )) 

Open in new window


I must presume that this part:
Upper('?Do you wish to use a site list?')
is actually filled by a variable:
Upper(@some_user_input)

in which case it makes sense. so, if the user says "NO" to that question, and the project is "ABC" (which may also be input, so actually not needed there), then return the rows from the main query.
in case the user does not specify "NO" (but "yes", for example), only the rows will be returned if the EXISTS condition is fulfilled.

this cannot be written much easier or more "efficiently".
0
 
colinspursAuthor Commented:
Thanks, I'll take this step by step.

Am I right in thinking the Upper is redundant, ie 'No' will equal 'NO' regardless?

Basically, the user can select individual sites or a site list, which contains sites.  I am trying to ascertain whether they want the choices to be mutually exclusive, ie if they want to use a site list the individual sites selected are ignored, or added to those in the site list.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Scott PletcherSenior DBACommented:
>> Am I right in thinking the Upper is redundant, ie 'No' will equal 'NO' regardless? <<

That depends on the SQL Server settings, and can vary even from one database to another.


The code above checks to see if they specified a list -- if they did not, then it verifies that the site they entered does exist in the site table.
0
 
colinspursAuthor Commented:
OK I'll keep it in then.

Yes, that is a variable entered by the user.  As are Project and Site_ID but I hard-coded them here to simplify the question.

What function does L.SITE_ID = F.SITE_ID play in the EXISTS clause, in the original code?

The GB_SITE_LIST table has a PK of Site_List_Name/Project/Site_ID, so theoretically, the same SITE_IDs could appear more than once, if they are in different projects.

I appreciate you may not have all the facts, if you need to know more detail please tell me.

Thanks,

  Col
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that part correlates the exists subquery to the main query.
so, logically, for each potential row returned by the main query, the subquery is executed with the value of L.SITE_ID as "input" value to determine if "exists" returns true or false, and hence to finally return the row from the main query or not.
0
 
colinspursAuthor Commented:
OK so as it stands, if the user does not enter a site_ID (remember I hard-coded this as 'XYZ' here) then even if they choose a site list that has site 'XYZ' in it, that will not be included because the correlation would fail?

I have to leave it overnight now - thanks again for your help, much appreciated.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>that will not be included because the correlation would fail?
it will actually be included if the correlation works.
otherwise you would have to put NOT EXISTS instead of EXISTS
0
 
colinspursAuthor Commented:
OK, I see the point of the join now...

I just want the ones in the site list, so why not incorporate it into an INNER JOIN as well, instead of EXISTS?

i.e.:

SELECT F.PROJECT, F.SITE_ID, F.SAMPLE_ID, F.FRACTION,
       W.RD_SINK, W.RD_FLOAT, W.FLOAT_YIELD, W.ASH,
       W.VOLATILES, W.MOISTURE, W.FIXED_CARBON,
       W.RESULT_SEQ, W.CALORIFIC_VALUE, W.TOTAL_SULPHUR,
       W.PHOSPHORUS
       
FROM GB_SITE_LIST AS L

INNER JOIN GB_SAMPLE_FRACTION AS F
      ON L.PROJECT = F.PROJECT
     AND L.SITE_ID = F.SITE_ID

INNER JOIN CL_WASH AS W ON F.PROJECT = W.PROJECT
                       AND F.SITE_ID = W.SITE_ID
                       AND F.SAMPLE_ID = W.SAMPLE_ID
                       AND F.FRACTION = W.FRACTION

WHERE L.SITE_LIST_NAME = '?Enter site list name?'
  AND F.FRACTION IN (?Select fraction?)
  AND W.WASH_TYPE = 'F'

ORDER BY F.PROJECT, F.SITE_ID, F.SAMPLE_ID, F.FRACTION, W.RD_SINK, W.RD_FLOAT
0
 
colinspursAuthor Commented:
Sorry for the delay!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now