The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.
Select from SubQuery
I have code that is inserting records into a temp table, Then using that those records in a second query:
select distinct(CASE_NO) into #Temp_Case_Attach_nlto
from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nlto%' --(43 row(s) affected)
select * from #Temp_Case_Attach_nlto
where CASE_NO not in
(select distinct(CASE_NO) from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nov%') -- 41 include this first section with the and
and CASE_NO not in
(select CASE_NO from Case_Main
where STATUS = 'UNFOUNDED') --41 ---Use this number for cases resolved voluntarily
drop table #Temp_Case_Attach_nlto
I want to create a query that will allow me to do the second query getting a count without using a temp table something like:
SELECT Distinct COUNT(*)
from
(SELECT Distinct CASE_NO
FROM Case_Attachments
Where DESCRIPTION like '%nlto%'
and CASE_NO in
(Select CASE_NO from Case_Actions
Where CASE_NO in (Select CASE_NO from Case_Main
Where STARTED >= '2015-06-01' and STARTED <= '2015-06-30')))
Where CASE_NO not in
(select distinct(CASE_NO) from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nov%')
and CASE_NO not in
(select CASE_NO from Case_Main
where STATUS = 'UNFOUNDED')
HOWEVER, It does not like the where clause after the select from.
select distinct(CASE_NO) into #Temp_Case_Attach_nlto
from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nlto%' --(43 row(s) affected)
select * from #Temp_Case_Attach_nlto
where CASE_NO not in
(select distinct(CASE_NO) from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nov%') -- 41 include this first section with the and
and CASE_NO not in
(select CASE_NO from Case_Main
where STATUS = 'UNFOUNDED') --41 ---Use this number for cases resolved voluntarily
drop table #Temp_Case_Attach_nlto
I want to create a query that will allow me to do the second query getting a count without using a temp table something like:
SELECT Distinct COUNT(*)
from
(SELECT Distinct CASE_NO
FROM Case_Attachments
Where DESCRIPTION like '%nlto%'
and CASE_NO in
(Select CASE_NO from Case_Actions
Where CASE_NO in (Select CASE_NO from Case_Main
Where STARTED >= '2015-06-01' and STARTED <= '2015-06-30')))
Where CASE_NO not in
(select distinct(CASE_NO) from Case_Attachments
where CASE_NO in
(select CASE_NO from Case_Actions
where CASE_NO in (select CASE_NO from Case_Main
where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and DESCRIPTION like '%nov%')
and CASE_NO not in
(select CASE_NO from Case_Main
where STATUS = 'UNFOUNDED')
HOWEVER, It does not like the where clause after the select from.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
>SELECT Distinct COUNT(*)
perhaps ... SELECT COUNT(DISTINCT ColumnNameGoesHere), as I don't believe you can do a DISTINCT on *
perhaps ... SELECT COUNT(DISTINCT ColumnNameGoesHere), as I don't believe you can do a DISTINCT on *
>where STARTED >='2015-06-01' and STARTED <= '2015-06-30'))
and if STARTED is a datetime, then the above would not return any rows with 2015-06-30 and a time component, so use this instead
where STARTED >='2015-06-01' and STARTED < '2015-07-01'))
and if STARTED is a datetime, then the above would not return any rows with 2015-06-30 and a time component, so use this instead
where STARTED >='2015-06-01' and STARTED < '2015-07-01'))
try:
SELECT DISTINCT cm.CASE_NO
FROM Case_Main cm
/*
INNER JOIN Case_Actions ca
ON cm.CASE_NO = ca.CASE_NO
*/
INNER JOIN Case_Attachments catt
ON cm.CASE_NO = catt.CASE_NO
WHERE cm.STARTED >= '2015-06-01'
AND cm.STARTED <= '2015-06-30'
AND catt.DESCRIPTION LIKE '%nlto%'
and cm.STATUS <> 'UNFOUNDED'
;
There is never a time component. I am working with older code, trying to rewite. I will try Pauls answer. Thanks for all of your help.
I think the more efficient query below will give you an accurate result, based on your stated requirements and the queries given:
SELECT COUNT(*) AS Final_Count
FROM (
SELECT cm.CASE_NO
FROM Case_Main cm
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
GROUP BY cm.CASE_NO
HAVING
SUM(CASE WHEN cm.DESCRIPTION like '%nlto%' THEN 1 ELSE 0 END) >= 1 AND
SUM(CASE WHEN cm.DESCRIPTION like '%nov%' THEN 1 ELSE 0 END) = 0 AND
SUM(CASE WHEN cm.STATUS = 'UNFOUNDED' THEN 1 ELSE 0 END) = 0
) AS subquery1
SELECT COUNT(*) AS Final_Count
FROM (
SELECT cm.CASE_NO
FROM Case_Main cm
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
GROUP BY cm.CASE_NO
HAVING
SUM(CASE WHEN cm.DESCRIPTION like '%nlto%' THEN 1 ELSE 0 END) >= 1 AND
SUM(CASE WHEN cm.DESCRIPTION like '%nov%' THEN 1 ELSE 0 END) = 0 AND
SUM(CASE WHEN cm.STATUS = 'UNFOUNDED' THEN 1 ELSE 0 END) = 0
) AS subquery1
i got 0. i should get 36. I will play with it and see if I can make adjustments to get a good result.
I went back to Paul's query and added some code. It worked. But maybe I could get some suggestions on a better way to do it.
SELECT DISTINCT cm.CASE_NO
FROM Case_Main cm
/*
INNER JOIN Case_Actions ca
ON cm.CASE_NO = ca.CASE_NO
*/
INNER JOIN Case_Attachments catt
ON cm.CASE_NO = catt.CASE_NO
WHERE cm.STARTED >= '2015-06-01'
AND cm.STARTED <= '2015-06-30'
AND catt.DESCRIPTION LIKE '%nlto%'
and cm.STATUS <> 'UNFOUNDED'
and CM.CASE_NO not in
(select Case_Main.CASE_NO from Case_Main, Case_Attachments
where CASE_MAIN.CASE_NO = Case_Attachments.CASE_NO
And STARTED >='2015-06-01' and STARTED <= '2015-06-30'
and Case_Attachments.DESCRIPTI
order by CASE_NO
SELECT DISTINCT cm.CASE_NO
FROM Case_Main cm
/*
INNER JOIN Case_Actions ca
ON cm.CASE_NO = ca.CASE_NO
*/
INNER JOIN Case_Attachments catt
ON cm.CASE_NO = catt.CASE_NO
WHERE cm.STARTED >= '2015-06-01'
AND cm.STARTED <= '2015-06-30'
AND catt.DESCRIPTION LIKE '%nlto%'
and cm.STATUS <> 'UNFOUNDED'
and CM.CASE_NO not in
(select Case_Main.CASE_NO from Case_Main, Case_Attachments
where CASE_MAIN.CASE_NO = Case_Attachments.CASE_NO
And STARTED >='2015-06-01' and STARTED <= '2015-06-30'
and Case_Attachments.DESCRIPTI
order by CASE_NO
Sorry, I overlooked that the DESCRIPTION was coming from the case_attachments table. But easy enough to join that table in (I had anticipated some type of join being needed, that's why I used a table alias in the query):
SELECT COUNT(*) AS Final_Count
FROM (
SELECT cm.CASE_NO
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
GROUP BY cm.CASE_NO
HAVING
SUM(CASE WHEN catt.DESCRIPTION like '%nlto%' THEN 1 ELSE 0 END) >= 1 AND
SUM(CASE WHEN catt.DESCRIPTION like '%nov%' THEN 1 ELSE 0 END) = 0 AND
SUM(CASE WHEN catt.STATUS = 'UNFOUNDED' THEN 1 ELSE 0 END) = 0
) AS subquery1
SELECT COUNT(*) AS Final_Count
FROM (
SELECT cm.CASE_NO
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
GROUP BY cm.CASE_NO
HAVING
SUM(CASE WHEN catt.DESCRIPTION like '%nlto%' THEN 1 ELSE 0 END) >= 1 AND
SUM(CASE WHEN catt.DESCRIPTION like '%nov%' THEN 1 ELSE 0 END) = 0 AND
SUM(CASE WHEN catt.STATUS = 'UNFOUNDED' THEN 1 ELSE 0 END) = 0
) AS subquery1
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
SELECT COUNT(*) AS Final_Count
FROM (
SELECT DISTINCT cm.CASE_NO
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
AND cm.STATUS <> 'UNFOUNDED'
AND catt.DESCRIPTION like '%nlto%'
AND NOT catt.DESCRIPTION like '%nov%'
) AS subquery1
{+ edit}
or more directly:
SELECT COUNT(DISTINCT cm.CASE_NO)
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
AND cm.STATUS <> 'UNFOUNDED'
AND catt.DESCRIPTION like '%nlto%'
AND NOT catt.DESCRIPTION like '%nov%'
FROM (
SELECT DISTINCT cm.CASE_NO
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
AND cm.STATUS <> 'UNFOUNDED'
AND catt.DESCRIPTION like '%nlto%'
AND NOT catt.DESCRIPTION like '%nov%'
) AS subquery1
{+ edit}
or more directly:
SELECT COUNT(DISTINCT cm.CASE_NO)
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
AND cm.STATUS <> 'UNFOUNDED'
AND catt.DESCRIPTION like '%nlto%'
AND NOT catt.DESCRIPTION like '%nov%'
Observation:
You seem to have a tendency to use IN ( some-query-here )
e.g.
and CM.CASE_NO not in
(select Case_Main.CASE_NO from Case_Main, Case_Attachments
where CASE_MAIN.CASE_NO = Case_Attachments.CASE_NO
And STARTED >='2015-06-01' and STARTED <= '2015-06-30'
and Case_Attachments.DESCRIPTI
Try to avoid this temptation
This might be difficult.
While using IN( ... ) isn't always bad, so far in this question each time there has been a more direct/more efficient alternative.
You seem to have a tendency to use IN ( some-query-here )
e.g.
and CM.CASE_NO not in
(select Case_Main.CASE_NO from Case_Main, Case_Attachments
where CASE_MAIN.CASE_NO = Case_Attachments.CASE_NO
And STARTED >='2015-06-01' and STARTED <= '2015-06-30'
and Case_Attachments.DESCRIPTI
Try to avoid this temptation
This might be difficult.
While using IN( ... ) isn't always bad, so far in this question each time there has been a more direct/more efficient alternative.
I tried your last query:
SELECT COUNT(*) AS Final_Count
FROM (
SELECT DISTINCT cm.CASE_NO
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
AND cm.STATUS <> 'UNFOUNDED'
AND catt.DESCRIPTION like '%nlto%'
AND NOT catt.DESCRIPTION like '%nov%'
) AS subquery1
It does not work. It stillretrieves all of the records (45), when it should get 36.
I would prefer not to use IN, but I was looking for something that would work. So that is why I posted that solution and then asked if there was a better way, since I did not know one. I do appreciate all the help I have obtained on this site.
SELECT COUNT(*) AS Final_Count
FROM (
SELECT DISTINCT cm.CASE_NO
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
AND cm.STATUS <> 'UNFOUNDED'
AND catt.DESCRIPTION like '%nlto%'
AND NOT catt.DESCRIPTION like '%nov%'
) AS subquery1
It does not work. It stillretrieves all of the records (45), when it should get 36.
I would prefer not to use IN, but I was looking for something that would work. So that is why I posted that solution and then asked if there was a better way, since I did not know one. I do appreciate all the help I have obtained on this site.
I thought there might be a way to do it with a left join and then look for a null, but I could not figure out exactly how to write it.
OK, using GROUP BY and HAVING removes some that a where clause does not, so try this one please
SELECT COUNT(*) AS Final_Count
FROM (
SELECT cm.CASE_NO
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
AND cm.STATUS <> 'UNFOUNDED'
GROUP BY cm.CASE_NO
HAVING SUM(CASE WHEN catt.DESCRIPTION like '%nlto%' THEN 1 ELSE 0 END) >= 1
AND SUM(CASE WHEN catt.DESCRIPTION like '%nov%' THEN 1 ELSE 0 END) = 0
) AS subquery1
SELECT COUNT(*) AS Final_Count
FROM (
SELECT cm.CASE_NO
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
WHERE cm.STARTED >='20150601' and cm.STARTED < '20150701'
AND cm.STATUS <> 'UNFOUNDED'
GROUP BY cm.CASE_NO
HAVING SUM(CASE WHEN catt.DESCRIPTION like '%nlto%' THEN 1 ELSE 0 END) >= 1
AND SUM(CASE WHEN catt.DESCRIPTION like '%nov%' THEN 1 ELSE 0 END) = 0
) AS subquery1
it does use a join....
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
there is no need for any more
FROM Case_Main cm
INNER JOIN Case_Attachments catt ON
catt.CASE_NO = cm.CASE_NO
there is no need for any more
I don't understand. The query at ID: 40916817 you stated it works. That is the code I am referring to.
That code does not contain IN ( ... )
That code does not contain IN ( ... )
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - PowerPoint 2013… 214 lessons
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
When you execute the query in SSMS, does return an error message? If yes, copy-paste it into this question, and click on the error message, watch the cursor jump to the offending line, and tell us what line that is.
btw That's a wompload of nested subqueries in the WHERE clause you have there.