SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 CTE with alot of left joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jasminegirl
Starting Member

7 Posts

Posted - 02/13/2010 :  16:38:40  Show Profile  Reply with Quote
I have heard about a CTE being a new feature in sql server 2005. Can you point me to a URL where a CTE can be used with alot of joins? I want to be able to use the CTE in 4 to 5 inner or left joins

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 02/13/2010 :  17:09:02  Show Profile  Reply with Quote
You mean something like this:
;WITH D1 AS
(SELECT s#, 
        COUNT(*) AS c1
   FROM @SB
  WHERE B#=1
  GROUP BY S#)
, D2 AS
(SELECT s#, 
        COUNT(*) AS c2
   FROM @SB
  WHERE B#=2
  GROUP BY S#)
, D3 AS
(SELECT s#, 
        COUNT(*) AS c3
   FROM @SB
  WHERE B#=3
  GROUP BY S#)

SELECT D.*, 
	 c1=ISNULL(c1,0), 
	 c2=ISNULL(c2,0), 
	 c3=ISNULL(c3,0)
  FROM @S AS D
  LEFT OUTER JOIN D1 
    ON D.S#=D1.S#
  LEFT OUTER JOIN D2 
    ON D.S#=D2.S#
  LEFT OUTER JOIN D3 
    ON D.S#=D3.S#;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/14/2010 :  03:51:32  Show Profile  Reply with Quote
CTE can be regarded like a temporary table with only exception that its available only in immediate query after which its declared. so you can use CTE as if you use a temporary table in joins but only restriction is that you can use it only in immediate statement that follows cte definition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jasminegirl
Starting Member

7 Posts

Posted - 02/14/2010 :  20:39:50  Show Profile  Reply with Quote
Thanks! this answers my question
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/15/2010 :  08:09:08  Show Profile  Reply with Quote
Cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000