Increment Variable within subquery

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)

Increment Variable within subquery

Author

Topic

blasterjumper
Starting Member

1 Post

Posted - 2007-09-18 : 09:23:30

Is there a way to increment a variable within a subquery? If I try something like this, I get an error.


DECLARE @Count int

SELECT a.field
FROM Table1 a
WHERE EXISTS
      (SELECT *
       FROM Table2 b
       WHERE b.field2 = 36 AND EXISTS 
                               (SELECT @Count = @Count + 1
                                FROM Table3 c 
                                WHERE c.field1 = b.field1 AND c.field = a.field         
                                )
        )

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 09:28:57

This?

SELECT		COUNT(DISTINCT c.Field1)
FROM		Table1 AS a
LEFT JOIN	Table2 AS b ON b.Field = a.Field
			AND b.Field2 = 36
LEFT JOIN	Table3 AS c ON c.Field1 = b.Field1

Or this?

SELECT		COUNT(DISTINCT c.Field1)
FROM		Table1 AS a
INNER JOIN	Table2 AS b ON b.Field = a.Field
INNER JOIN	Table3 AS c ON c.Field1 = b.Field1
WHERE		b.Field2 = 36

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources