Can this query be expressed without using a CTE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can this query be expressed without using a CTE

Author

Topic

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2014-11-10 : 23:15:45

Hello I have this query that I need to integrate into a larger subquery and I cant use a cte. I was able to get the results writing the query using a cte for I cant seem to get the desired results using a series of subs, can anyone shed some light....thanks


WITH cte1 (ID, CN, MK)
AS
(
SELECT s.ID, h.CN, h.MK  

FROM STU s
LEFT JOIN 
HIS h ON s.ID = h.PID

WHERE CN IN ('C10001', 'C10301', 'C10601', 'C11001', 'C14001', 'C17001') 
AND s.SC = 30 AND s.GR = 11
),

cte2 (ID, CN, MK)
AS
(
SELECT s.ID, h.CN, h.MK  

FROM STU s
LEFT JOIN 
HIS h ON s.ID = h.PID

WHERE CN IN ('320051', 'C10002', 'C10302', 'C10602', 'C11002', 'C14002', 'C17002', 'C20001', 'C20601', 'C20602', 'C20701', 
'C20702', 'C21001', 'C21002', 'C24001') AND s.SC = 30 AND s.GR = 11
)

SELECT ID, CASE WHEN Alg1 = 1 AND Alg2 = 1 THEN 1 END AS MATH
FROM
(
SELECT c1.ID, CASE WHEN c1.MK  IN ('F', 'F+', 'F-', 'D', 'D-', 'D+', 'NM', 'NC') THEN 1 ELSE 0 END AS Alg1,

CASE WHEN c2.MK NOT IN ('F', 'F+', 'F-', 'D', 'D-', 'D+', 'NM', 'NC') THEN 1 ELSE 0 END AS Alg2

FROM cte1 c1 LEFT JOIN cte2 c2 ON c1.ID = c2.ID
)
AS a

Cartesian Yak

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-11 : 03:48:48

basically just cut & paste

SELECT ID, CASE WHEN Alg1 = 1 AND Alg2 = 1 THEN 1 END AS MATH
FROM
(
	SELECT 	c1.ID, CASE WHEN c1.MK  IN ('F', 'F+', 'F-', 'D', 'D-', 'D+', 'NM', 'NC') THEN 1 ELSE 0 END AS Alg1,
		CASE WHEN c2.MK NOT IN ('F', 'F+', 'F-', 'D', 'D-', 'D+', 'NM', 'NC') THEN 1 ELSE 0 END AS Alg2

	FROM 	(
			SELECT 	  s.ID, h.CN, h.MK  
			FROM 	  STU s
			LEFT JOIN HIS h ON s.ID = h.PID
			WHERE 	  CN IN ('C10001', 'C10301', 'C10601', 'C11001', 'C14001', 'C17001') 
			AND 	  s.SC = 30 AND s.GR = 11
		) c1 
		LEFT JOIN 
		(
			SELECT 	  s.ID, h.CN, h.MK  
			FROM 	  STU s
			LEFT JOIN HIS h ON s.ID = h.PID
			WHERE 	CN IN ('320051', 'C10002', 'C10302', 'C10602', 'C11002', 'C14002', 'C17002', 'C20001', 'C20601', 'C20602', 'C20701', 
					'C20702', 'C21001', 'C21002', 'C24001') 
			AND 	s.SC = 30 AND s.GR = 11
		) c2 ON c1.ID = c2.ID
) AS a

KH
[spoiler]Time is always against us[/spoiler]

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2014-11-11 : 16:18:47

Thanks khtan, looks great!

Cartesian Yak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources