not inserting duplicate - 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 2000 Forums

Transact-SQL (2000)

not inserting duplicate

Author

Topic

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2007-02-06 : 05:09:23

Hi

i have a table1
table1
tid id
101 1
101 3
102 2

table2
id ident_name
1 name
2 test
3 qa

i used this query
select b.tid, a.ident_name from table1 as a join table2 as b on a.id=b.id where tskid= 101

i got result
101 name
101 qa

now i don't want to add duplicate ident_name in table1 for tid '101'

please suggest me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 05:18:09

Are you pulling our legs?

quote:
Originally posted by putane.sanjay

select b.tid, a.ident_name from table1 as a join table2 as b on a.id=b.id where tskid= 101

In your query, you ALIAS the Table1 table as a, and Table2 table as b, but the column ident_name does exist in Table2 (b), not in (a) as you have wrote.

If you do not want duplicate ident_names for a tid, what do you want instead?

Peter Larsson
Helsingborg, Sweden

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2007-02-06 : 05:34:07

sorry while writing I made mistake actual query
is this
select b.tid, a.ident_name from table1 as b join table2 as a on a.id=b.id where tskid= 101

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 05:36:16

And my other question?
"If you do not want duplicate ident_names for a tid, what do you want instead?"

Peter Larsson
Helsingborg, Sweden

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2007-02-06 : 05:46:57

I have to give some other names other than 'name' ,'qa', 'test'
From the stored procedures if i pass this values
parameter @tid= 101 and @ident_name='name' it must not take 'name' for the ident_name column in the 'table2 ' and it shpuld give message that "you should not add duplicate ident_name for the @tid=101"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 05:55:21

Where is your stored procedure?

Peter Larsson
Helsingborg, Sweden

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2007-02-06 : 05:57:14

i have to write now

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2007-02-06 : 05:59:23

iam new for writting stored procedure and iam struggling

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-06 : 06:04:38

[code]IF Exists(Select t1.* from table1 t1 Join Table2 t2 on t1.[ID] = t2.[ID] Where t1.tid = @tid and t2.ident_name = @ident_name)
Begin
RAISERROR('Duplicate Entry...', 16,1)
Return
END[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2007-02-06 : 08:29:42

thank you for your suggestions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources