Solved

Simplify T-SQL query

Posted on 2014-03-17
10
126 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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

15 Experts available now in Live!

Get 1:1 Help Now