Subquery question 1

[ghbeers]

I'm trying NOT to create a temp table to do what I need because my 3rd party reporting software will require a stored procedure, and yet the 3rd party software has a bug preventing me to do so.

So, I was hoping to be able to do something like you see below. Is this possible? I'm definitely having trouble making a more complex version of this sample's structure work.

SELECT
ID,
NAME,
(SELECT TOP 1 TABLE1.TERM FROM TABLE1
WHERE TABLE1LTERM NOT IN
(SELECT DISTINCT TABLE2.TERM2 FROM TABLE2
WHERE TABLE2.TYPE <> 'XYZ')
AND TABLE1.DATE > '01/01/2015'
ORDER BY TABLE1.DATE)

 

[SgtJarrow]

Can you provide sample table schema, keys and a few sample records in each? Some expected output would help as well.

I believe you/we are missing some information in order to assist you. Your base table doesn't exist and doesn't really have a join to the derived query.

SELECT ID, NAME, (complex code) FROM WHAT

Robert "Wizard" Johnson III
U.S. Military Vets MC
Data Integration Engineer

 

[ghbeers]

ABBREVIATION OF ACTUAL QUERY i WOULD LIKE TO HAVE, BUT DOESN'T WORK

TABLES ARE A LITTLE TOO COMPLEX TO GIVE EXAMPLES....

SELECT
P.ID
(SELECT TOP 1 S.STC_TERM
FROM STUDENT_ACAD_CRED S
LEFT JOIN STC_STATUSES SS ON S.STUDENT_ACAD_CRED_ID = SS.STUDENT_ACAD_CRED_ID
AND SS.POS = 1
AND SS.STC_STATUS IN ('A','N')

-- I want the earliest term here - sort by ascending
WHERE S.STC_TERM NOT IN

-- Get all terms where a high school class was taken

(SELECT DISTINCT S2.STC_TERM
FROM STUDENT_ACAD_CRED S2
LEFT JOIN STC_STATUSES S2S ON S2.STUDENT_ACAD_CRED_ID = S2S.STUDENT_ACAD_CRED_ID
AND S2S.POS = 1
AND S2S.STC_STATUS IN ('A','N')
LEFT JOIN STUDENT_COURSE_SEC SC2 ON S2.STC_STUDENT_COURSE_SEC = SC2.STUDENT_COURSE_SEC_ID
JOIN COURSE_SECTIONS_LS CS2 ON SC2.SCS_COURSE_SECTION = CS2.COURSE_SECTIONS_ID
AND CS2.SEC_COURSE_TYPES LIKE 'HSD%'
OR CS2.SEC_COURSE_TYPES LIKE 'MSA%'
WHERE S2.STC_PERSON_ID = P.ID)

AND S.STC_CRED_TYPE NOT LIKE 'UC%'
AND S.STC_PERSON_ID = 'O347310'

ORDER BY S.STC_START_DATE) as 'Non-Dual Start Term'

FROM PERSON P
WHERE P.ID = 'O237161'

 

[fredericofonseca]

Saying it doesn't work does not help if you don't say exactly why you think it does not work.
do you get an error?
do you get incorrect results?

With regards to your code - formatted and with tgml tags so its clear what you trying to do
Apart from the comments the code looks to be a valid sql construct

select p.id
   (select top 1 S.STC_TERM
    from STUDENT_ACAD_CRED S
[COLOR=#EF2929]
    -- this left join is redundant here - the top 1 select is not from this table, and the where clause makes no reference to it - being a left join means that regardless of the results you only get the where clause as a filter
    left join STC_STATUSES SS 
      on S.STUDENT_ACAD_CRED_ID = SS.STUDENT_ACAD_CRED_ID
      and SS.POS = 1
      and SS.STC_STATUS in ('A', 'N')
[/color]
    where S.STC_TERM not in (select distinct S2.STC_TERM
                             from STUDENT_ACAD_CRED S2
                             left join STC_STATUSES S2S
                               on S2.STUDENT_ACAD_CRED_ID = S2S.STUDENT_ACAD_CRED_ID
                              and S2S.POS = 1
                              and S2S.STC_STATUS in ('A', 'N')
                             left join STUDENT_COURSE_SEC SC2 
                               on S2.STC_STUDENT_COURSE_SEC = SC2.STUDENT_COURSE_SEC_ID
                             join COURSE_SECTIONS_LS CS2 
                               on SC2.SCS_COURSE_SECTION = CS2.COURSE_SECTIONS_ID

                               [COLOR=#EF2929] -- is this correct?[/color] 
                               and CS2.SEC_COURSE_TYPES like 'HSD%'
                                or CS2.SEC_COURSE_TYPES like 'MSA%'

                               [COLOR=#EF2929]-- or should it be
                               and (  CS2.SEC_COURSE_TYPES like 'HSD%'
                                   or CS2.SEC_COURSE_TYPES like 'MSA%'
                                    )[/color]
   
                             where S2.STC_PERSON_ID = p.id
                            )
     and S.STC_CRED_TYPE not like 'UC%'
     and S.STC_PERSON_ID = [COLOR=#EF2929]'O347310'[/color] -- this is not the same as the P.ID below - is that correct?
     order by S.STC_START_DATE
     ) as 'Non-Dual Start Term'

from PERSON p
where p.id = 'O237161'

Possible way to rewrite the query - which may or not make it faster.
without knowing the the data, table definitions and relations, and exactly what you trying to achive with this
there is no guarantee that this meets the requirements

--     and S.STC_PERSON_ID = 'O347310' if this really needs to be this person then this query is not valid

select p.id
     , term.STC_TERM as 'Non-Dual Start Term'
from PERSON p
left outer join 
 (select s.STC_PERSON_ID
       , S.STC_TERM
       , row_number() over(partition by s.STC_PERSON_ID
                               order by S.STC_START_DATE
                          ) as rownum
    from STUDENT_ACAD_CRED S
    left join STC_STATUSES SS 
      on S.STUDENT_ACAD_CRED_ID = SS.STUDENT_ACAD_CRED_ID
      and SS.POS = 1
      and SS.STC_STATUS in ('A', 'N')

    where S.STC_TERM not in (select distinct S2.STC_TERM
                             from STUDENT_ACAD_CRED S2
                             left join STC_STATUSES S2S
                               on S2.STUDENT_ACAD_CRED_ID = S2S.STUDENT_ACAD_CRED_ID
                              and S2S.POS = 1
                              and S2S.STC_STATUS in ('A', 'N')
                             left join STUDENT_COURSE_SEC SC2 
                               on S2.STC_STUDENT_COURSE_SEC = SC2.STUDENT_COURSE_SEC_ID
                             join COURSE_SECTIONS_LS CS2 
                               on SC2.SCS_COURSE_SECTION = CS2.COURSE_SECTIONS_ID
                               and CS2.SEC_COURSE_TYPES like 'HSD%'
                                or CS2.SEC_COURSE_TYPES like 'MSA%'
                             where S2.STC_PERSON_ID = s.STC_PERSON_ID -- is this correct? if the " S.STC_PERSON_ID = 'O347310'" is correct on the original query the probably not
                            )
     and S.STC_CRED_TYPE not like 'UC%'
--     and S.STC_PERSON_ID = 'O347310' if this really needs to be this person then this query
     ) term
on term.STC_PERSON_ID = p.id
and term.rownum = 1 -- this returns a single record per ID
where p.id = 'O237161'

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[kjv1611]

If you want to do something different than a subquery without creating a temp table, just use a CTE. That was introduced in SQL 2005 or 2008. It's a great tool.

You can use multiple CTEs as well. This way you get the logic structure of tables in your query without creating temp tables. Without trying to recreate or guess at your code, I'll just toss out a simple sample:

;WITH FordCars AS
(
     SELECT CarID ,Color FROM AllCars WHERE Mfg = 'Ford'
)

, GMCars AS
(
     SELECT CarID ,Weight FROM AllCars WHERE Mfg = 'GM'
)

SELECT     AllCars.SomeField
[indent],[indent]FordCars.Color[/indent][/indent]
[indent],[indent]GMCars.Weight[/indent][/indent]
FROM AllCars a
JOIN FordCards f ON a.CarID = f.CarID
JOIN GMCars g ON a.CarID = g.CarID

Hopefully, that gets the idea across in a rather silly example.

One thing to point out, though: Depending upon your query details, table size, etc, sometimes a CTE or SubQuery can lead to performance issues. You're basically hitting the same table multiple times. But if you use it right, it can offer better performance in many situations. I like to use CTEs often against large tables just to give an initial hit filter that helps me run a query in seconds that would otherwise take minutes.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[ghbeers]

Yesterday I began to build the CTE approach that kjv1611 suggests to see if it's a better choice, and will continue this morning. I'll also apply Frederico's recommendations. If both are viable methods, it'll come down to which executes more efficiently, because this single set of logic in my query will be a significant resource hog. I only submitted the portion of the query that is giving me problems, so I have to consider the impact of overall resources consumed.

Thank you both very much.

 

[kjv1611]

One more far-fetched possibility if you can include action queries. In different circumstances, CTEs and SubQueries will perform better, while in others temp tables, and in very rare circumstances, even table variables. The latter, in my opinion, gets more complicated, but can occasionally help. Just don't dump a large dataset to a table variable or else you're causing more damage to runtime. Where a table variable seems to fit best is if you have a semi-short "list" you're wanting to pull from another table and need to reference it multiple times. In that instance, you hit the source table once, and the values are available however often you need. That's better than a CTE and sometimes better than a temp table if you need to reference it multiple times. The reason it can be better than a temp table is the table variable is already in RAM vs disk, where the temp table is typically on disk. That's another reason why it's best to avoid larger datasets in a table variable. But I digress... a lot.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[ghbeers]

I ended up going with the CTE method. It works well and is very fast. Thank you all for your suggestions.