Retrive names not exist in other table

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)

Retrive names not exist in other table

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-04-17 : 03:25:03

I want to find all last names in the LN table that are not in the FN table..…output format should have LN, culture, continent

LN Table
--------
LastName culture Continent
gorijala indi asia
ding chin asia

FN Table
--------
FristName culture Continent
satish indi asia
twwen jpan asia
ding jpan asia

output
------
LastName culture Continent
gorijala indi asia

Both tables contains lakhs of records

G. Satish

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-17 : 04:36:30

try this out pls

select l.lastname,l.culture,l.continent from ln l left join fn f
on l.lastname=f.firstname where f.firstname is null

tanx...

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-17 : 04:39:30

DECLARE @Table1 TABLE(LastName VARCHAR(10),CULT VARCHAR(10), CONT VARCHAR(10))
DECLARE @Table2 TABLE(FirstName VARCHAR(10),CULT VARCHAR(10), CONT VARCHAR(10))

INSERT INTO @Table1 VALUES('gorijala', 'indi', 'asia')
INSERT INTO @Table1 VALUES('ding', 'chin', 'asia')

INSERT INTO @Table2 VALUES('satish ', 'indi', 'asia')
INSERT INTO @Table2 VALUES('satish ', 'jpan', 'asia')
INSERT INTO @Table2 VALUES('ding ', 'jpan', 'asia')

SELECT * FROM @Table1 WHERE LastName NOT IN
(SELECT FirstName FROM @Table2)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 05:04:22

Dont use NOT IN

LEFT JOIN is more effecient

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 10:49:35

you can use NOT EXISTS also

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources