Select from SubQuery

qbjgqbjg
qbjgqbjg used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Define 'not like', preferably with an error statement.  Doesn't get invited to the 'cool' parties?  Doesn't get invited to the 'cabin Up North' to see the family and go water skiing?  

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.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>SELECT Distinct COUNT(*)
perhaps ... SELECT COUNT(DISTINCT ColumnNameGoesHere), as I don't believe you can do a DISTINCT on *
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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'))
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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'
;

Open in new window

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
qbjgqbjgConsultant

Author

Commented:
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.
qbjgqbjgConsultant

Author

Commented:
That works for the 1st query, but I need to use it to select from to get the 2nd query.
qbjgqbjgConsultant

Author

Commented:
It is getting all the records from the 1st query. Some should be eliminated.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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
qbjgqbjgConsultant

Author

Commented:
i got 0. i should get 36. I will play with it and see if I can make adjustments to get a good result.
qbjgqbjgConsultant

Author

Commented:
Case_Attachments is not considered in Scott's query.
qbjgqbjgConsultant

Author

Commented:
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.DESCRIPTION like '%nov%')
order by CASE_NO
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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
qbjgqbjgConsultant

Author

Commented:
Status is CM.Status, but yes, it works now.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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%'
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.DESCRIPTION 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.
qbjgqbjgConsultant

Author

Commented:
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.
qbjgqbjgConsultant

Author

Commented:
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.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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
qbjgqbjgConsultant

Author

Commented:
Yes, That works. Is there a way to write it using a join?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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
qbjgqbjgConsultant

Author

Commented:
I meant instead of the not in.
qbjgqbjgConsultant

Author

Commented:
The records in the not in select are a group of records. I meant using a join to that select.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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 ( ... )

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial