Is this equal to inner join - 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 2008 Forums

Transact-SQL (2008)

Is this equal to inner join

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2012-08-14 : 07:37:35

Hi ,
Why isn't this piece of code equal to inner joining three tables
I want to have subquery columns in result by changing it to inner join
I use "and" for common columns in "ON" statement
but get differnt result

 SELECT * 
                      from table1 dd
                     where (dd.c,dd.i) in(select ply.c,rply.i
                          from ply ,rply 
                         where rply.s in ('C','K')
                           and ply.f=rply.f                         
                           and ply.code=rply.code)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-14 : 07:57:42

It is not a valid statement at all.

SELECT	t.*
FROM	dbo.Table1 AS t
WHERE	EXISTS	(
			SELECT		*
			FROM		dbo.Ply AS p
			INNER JOIN	dbo.rPly AS r ON r.f = p.f
						AND r.Code = p.Code
						AND r.s IN ('C', 'K')
			WHERE		t.c = p.c
					AND t.i = r.i
		)

And not, is not like an inner join, because with inner join you do filter and you can also get multiple rows back.
In the query above you do filter but you cannot get multiple rows back.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources