Colin Brazier
asked on
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'
)
)
)
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'
)
)
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
>> 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.
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.
ASKER
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/Sit e_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
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/Sit
I appreciate you may not have all the facts, if you need to know more detail please tell me.
Thanks,
Col
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.
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.
ASKER
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.
I have to leave it overnight now - thanks again for your help, much appreciated.
>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
it will actually be included if the correlation works.
otherwise you would have to put NOT EXISTS instead of EXISTS
ASKER
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
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
ASKER
Sorry for the delay!!
(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.