CTE with alot of left joins - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

CTE with alot of left joins

Author

Topic

jasminegirl
Starting Member

7 Posts

Posted - 2010-02-13 : 16:38:40

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 - 2010-02-13 : 17:09:02

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#;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 03:51:32

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/

jasminegirl
Starting Member

7 Posts

Posted - 2010-02-14 : 20:39:50

Thanks! this answers my question

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 08:09:08

Cheers

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources