Select Statement to capture data from two tables

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

General SQL Server Forums

New to SQL Server Programming

Select Statement to capture data from two tables

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2012-07-25 : 09:14:31

I have table 1 with columns "FIRST_NAME" and "LAST_NAME"
table 2 with columns "FIRST_NAME" and "MIDDLE_INT"

I need a query that can grab all "FIRST_NAME" in table 1 that ARE NOT in table 2 and include an ID column listing them as 1,2,3,4,5,6 etc.

Any help on this issue would be great.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-25 : 09:19:35

select id, first_name
from tbl1
where first_name not in (select first_name) from tbl2)

or maybe
select distinct first_name, id = row_number() over (order by first_name)
from tbl1
where first_name not in (select first_name) from tbl2)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Johnph
Posting Yak Master

103 Posts

Posted - 2012-07-25 : 09:27:33

Hey, the second query worked perfectly. Thank you!

Johnph
Posting Yak Master

103 Posts

Posted - 2012-07-25 : 09:35:06

Actually, is there a way to make the ID column varchar instead of BIGINT?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-25 : 10:21:44

select first_name, id = converrt(varchar(20),id)
from
(
select distinct first_name, id = row_number() over (order by first_name)
from tbl1
where first_name not in (select first_name) from tbl2)
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources