qbjgqbjg
asked on
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.
>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'
;
Note, there is no apparent reason for including the table Case_Actions in the query as you are only filtering on fields in Case_Main & Case_Attachments
ASKER
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.
ASKER
That works for the 1st query, but I need to use it to select from to get the 2nd query.
ASKER
It is getting all the records from the 1st query. Some should be eliminated.
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
ASKER
i got 0. i should get 36. I will play with it and see if I can make adjustments to get a good result.
ASKER
Case_Attachments is not considered in Scott's query.
ASKER
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 ON like '%nov%')
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Status is CM.Status, but yes, it works now.
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 ON like '%nov%')
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.
ASKER
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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, That works. Is there a way to write it using a join?
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
ASKER
I meant instead of the not in.
ASKER
The records in the not in select are a group of records. I meant using a join to that select.
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 ( ... )
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.