?
Solved

Simplify T-SQL query

Posted on 2014-03-17
10
Medium Priority
?
139 Views
Last Modified: 2014-12-05
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
Comment
Question by:colinspurs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39934569
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39934590
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
 
LVL 3

Author Comment

by:colinspurs
ID: 39934657
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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39934722
>> 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
 
LVL 3

Author Comment

by:colinspurs
ID: 39934806
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39934827
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
 
LVL 3

Author Comment

by:colinspurs
ID: 39934877
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39936214
>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
 
LVL 3

Author Comment

by:colinspurs
ID: 39937363
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
 
LVL 3

Author Closing Comment

by:colinspurs
ID: 40482554
Sorry for the delay!!
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question