Need help to learn cte usage

[djj55]

I have two tables joined on CallGUID
[tt]
TABLE1
CallType
CallDate
CallNumber
CallDuration
CallGUID

CallType CallDate CallNumber CallDuration CallGUID
1 08/12/2010 09:20:38 1234561234 100 8C2166F4
0 08/12/2010 11:20:38 1234561236 90 8C2199F6

TABLE2
CallExt - shows incoming line (<100) and answering extention
CallGUID

CallExt CallGUID
7 8C2166F4
1000 8C2166F4
1700 8C2166F4
20 8C2199F6
1810 8C2199F6
[/tt]
I would like the output table to be
[tt]
OUTTABLE
CallType
CallDate
CallNumber
CallDuration
CallExt - answering extention
CallExt - incoming line

CallType CallDate CallNumber CallDuration CallExt CallExt
1 08/12/2010 09:20:38 1234561234 100 1000 7
1 08/12/2010 09:20:38 1234561234 100 1700 7
0 08/12/2010 11:20:38 1234561236 90 1810 20
[/tt]
Thus separating the incoming line from the answering extention.
Note the incoming line is less than 100.

I think a cte will take care of this but I still learning how to use them correctly.

My current idea

SELECT A.CallType, A.CallDate, A.CallNumber, A.CallDuration, B.CallExt, C.CallExt
FROM TABLE1 A 
INNER JOIN (
 SELECT CallExt, CallGUID
 FROM TABLE2 
 WHERE CAST(CallExt AS INT) > 100
) AS B 
ON A.CallGUID = B.CallCUID 
INNER JOIN (
 SELECT CallExt, CallGUID
 FROM TABLE2 
 WHERE CAST(CallExt AS INT) < 100
) AS C 
ON A.CallGUID = C.CallCUID

Thank you for your help.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Wallie12]

SELECT * FROM Employees

-- Common Table Expression (CTE) can be thought as an inline view similar to a derived table
SELECT *
FROM (
SELECT EmployeeId AS EmpId, FirstName + ' ' + LastName AS FullName, ReportsTo AS YourBoss
FROM Employees
) AS x

SELECT * FROM x
/*
1) Runs the derived table query and creates a server-side temporary result set
2) query that ss-t-rs in the outer or main query
*/
;
WITH OurEmployees ( EmpId, FullName, YourBoss )
AS
(
SELECT EmployeeId AS EmpId, FirstName + ' ' + LastName AS FullName, ReportsTo AS YourBoss
FROM Employees
)

SELECT * FROM OurEmployees
or
USE Northwind
;
-- Your place in the pecking order:
-- Recursive CTE
WITH MyEmployees ( EmployeeId, LastName, Boss, HierOrder, ReportsTo )
AS
(
-- define "anchor member"
SELECT EmployeeId, LastName, LastName AS Boss, 1 AS HierOrder, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL -- UNION "recursive members"
-- "recursive member"
SELECT e.EmployeeId, e.LastName, m.LastName, HierOrder + 1 , e.ReportsTo
FROM Employees AS e JOIN MyEmployees m
ON e.ReportsTo = m.EmployeeId -- e.ReportsTo is Child to m.EmployeeId -- includes our "termination check"
WHERE e.ReportsTo IS NOT NULL
)
SELECT * FROM MyEmployees

/*
CTE Processing Sequence:
1) Creates the temporary server-side result (MyEmployees)
2) Insert the Anchor Member row(s) into MyEmployees
3) Inserts the recursive members by Joining the base table to MyEmployees and
execute the termination check until there are no more rows

Step 1: Set HeirOrder = 1 and inserts Fuller
Step 2: Increase HierOrder to 2 and insert any employees who report to Fuller
Step 3: Increase HeirOrder to 3 and insert any employees who report to anyone with HierOrder=2
-- insert as HierOrder=3
Step 4: repeat this process until you get to the level where no one reports to someone a level above them
*/

 

[djj55]

Thank you for your reply.

So, cte is used more for a hierarchy or recursive need.

Now to remember this.

By the way I liked how you presented your example as I could understand it.

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Rhys666]

A CTE is so much more than that really. Some useful reading for later...

Using Common Table Expressions
WITH common_table_expression (Transact-SQL)
Common Table Expressions (CTE) in SQL Server 2005 By Scott Mitchell
Data Points:Common Table Expressions (MSDN Magazine)

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[markros]

One more blog (with additional links inside):
CTE and hierarchical queries

PluralSight Learning Library

 

[djj55]

Thanks all, I had read the BOL and Microsoft take on cte but I do not like how they present there "help".

I get more from "real" world examples and these blogs provide that.

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!