Solved

Simplify T-SQL query

Posted on 2014-03-17
10
128 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
  • 5
  • 3
  • 2
10 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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
 
LVL 69

Expert Comment

by:ScottPletcher
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 142

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 142

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql joining from the same table 6 29
Make query more efficient 1 16
Updating a column using a lookup on another table. 6 16
TSQL - IF ELSE? 3 26
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now