SQL Expression (opposite inner join)

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

Transact-SQL (2000)

SQL Expression (opposite inner join)

Author

Topic

pankaj.pareek@sparrowi.co
Starting Member

26 Posts

Posted - 2007-05-22 : 08:51:29

Dear All

We need to find those records that do not belong to the result of the following query.

My Query is :

"SELECT A.Project_ID, A.Plot, A.Floor FROM dbo.Greens A inner JOIN dbo.tblUsers B ON A.Project_ID = B.Project_ID AND A.Plot = B.Plot AND A.Floor = B.Floor"

Thanks.

Rishi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 08:56:25

[code]
SELECT A.Project_ID, A.Plot, A.Floor
FROM dbo.Greens A LEFT JOIN dbo.tblUsers B
ON A.Project_ID = B.Project_ID
AND A.Plot = B.Plot
AND A.Floor = B.Floor
WHERE B.Project_ID IS NULL
[/code]

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-22 : 08:57:58

You can use the NOT IN expression:

SELECT * FROM dbo.Greens
WHERE Project_ID NOT IN (
SELECT A.Project_ID FROM dbo.Greens A inner JOIN dbo.tblUsers B ON A.Project_ID = B.Project_ID AND A.Plot = B.Plot AND A.Floor = B.Floor) A

SQL Server Helper
http://www.sql-server-helper.com

pankaj.pareek@sparrowi.co
Starting Member

26 Posts

Posted - 2007-05-23 : 00:39:57

Thanks.
Rishi Sharma

quote:

Originally posted by khtan


SELECT A.Project_ID, A.Plot, A.Floor 
FROM   dbo.Greens A LEFT JOIN dbo.tblUsers B 
ON     A.Project_ID = B.Project_ID 
AND    A.Plot       = B.Plot 
AND    A.Floor      = B.Floor
WHERE  B.Project_ID IS NULL

Dallr
Yak Posting Veteran

87 Posts

Posted - 2007-05-23 : 07:57:04

The LEFT JOIN would also be faster than the "NOT IN"

Dallr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources