need help with code - 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

General SQL Server Forums

New to SQL Server Programming

need help with code

Author

Topic

thisissin
Starting Member

1 Post

Posted - 2010-04-20 : 20:15:59

i am trying to figure out a code for a scenario.

i have 2 tables(jam and Dun) with 3 columns(Last name, First name, and Bar Code). Bar code column is represented in ID numbers that are not unique.

i need to list all the names of people that show up in both tables but the bar code number is only in table D. that way i can print this list and enter the bar code numbers into table j manually which is an excel sheet.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 00:19:19

Just try this:

Declare @jam table
(
Last_name varchar(50),
First_name varchar(50),
Bar_Code int
)

Declare @Dun table
(
Last_name varchar(50),
First_name varchar(50),
Bar_Code int
)

Insert into @jam
Select 'Test1','Test2',1 union all
Select 'Test2','Test2',123 union all
Select 'Test3','Test4',null union all
Select 'Test4','Test5',235

Insert into @Dun
Select 'Test1','Test2',1 union all
Select 'Test2','Test2',123 union all
Select 'Test3','Test4',245 union all
Select 'Test4','Test5',235

Select D.Last_name, D.First_name from @Dun d inner join @jam j on d.Last_name= j.Last_name
and d.First_name = j.First_name and j.Bar_code is null

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:25:00

you can just do an update to get values onto jam

something like


update j
set j.[Bar Code]=d.[Bar Code]
from jam j
join Dun d
on d.[Last name] =j.[Last name]
and d.[First name] = j.[First name]
where NULLIF(j.[Bar Code],'') IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 02:22:38

quote:
Originally posted by visakh16

you can just do an update to get values onto jam

something like

update j
set j.[Bar Code]=d.[Bar Code]
from jam j
join Dun d
on d.[Last name] =j.[Last name]
and d.[First name] = j.[First name]
where NULLIF(j.[Bar Code],'') IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think that OP don't want to update the records. He just wants the details where BarCode value exists only in one table.
Please correct me if my understanding is wrong.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 05:42:42

quote:
Originally posted by pk_bohra

quote:
Originally posted by visakh16

you can just do an update to get values onto jam

something like

update j
set j.[Bar Code]=d.[Bar Code]
from jam j
join Dun d
on d.[Last name] =j.[Last name]
and d.[First name] = j.[First name]
where NULLIF(j.[Bar Code],'') IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think that OP don't want to update the records. He just wants the details where BarCode value exists only in one table.
Please correct me if my understanding is wrong.

seems like you're

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources