July 21, 2009 at 8:17 am
Hello Everyone,
I have been performing some insert operation on a table containing composite pk key and stuck with where clause condition. example table is given below
create table a
(
oid varchar(2),
sid varchar(2),
rid int,
id int,
constraint pk_a primary key (oid,sid,rid)
) on [Primary]
insert into a (oid,sid,rid,id) values ('AB','CD',1,1)
insert into a (oid,sid,rid,id) values ('BA','DC',2,1)
insert into a (oid,sid,rid,id) values ('AB','CD',2,1)
INSERT into a (oid,sid,rid,id) values ('AB','CD',1,1)
create table #b
(
oid varchar(2),
sid varchar(2),
rid int,
id int
)
insert into #b (oid,sid,rid,id) values ('AB','CD',1,1)
insert into #b (oid,sid,rid,id) values ('BA','DC',2,1)
insert into #b (oid,sid,rid,id) values ('AB','CD',2,1)
insert into #b (oid,sid,rid,id) values ('AB','CD',3,1)
insert into #b (oid,sid,rid,id) values ('BA','DC',3,1)
insert into #b (oid,sid,rid,id) values ('AB','CD',3,1)
In table a oid,sid,rid uniquely identify the row . I need to insert only those rows from #b which are not in a satifying the above unique condition.
"select r.oid,r.sid,r.rid from #b r
where (r.rid not in (select rid from a) and r.sid collate latin1_general_ci_as not in (select sid from a) )" -- this would defintely not work????
so the result should be
insert into #b (oid,sid,rid,id) values ('AB','CD',3,1)
insert into #b (oid,sid,rid,id) values ('BA','DC',3,1)
insert into #b (oid,sid,rid,id) values ('AB','CD',3,1)
Any suggestion will be helpful
Regards,
vidhya
July 21, 2009 at 8:21 am
Does this work?
SELECT r.oid,r.sid,r.rid from #b r
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.oid = r.oid AND a.sid = r.sid AND a.rid = r.rid)
July 21, 2009 at 8:25 am
Hi Matt ,
yeh, It worked... My brain should have been drained out !!!! 🙂
July 21, 2009 at 12:39 pm
Also, from what I've read and learned and experienced, it would be better to use Inner Joins rather than joining within the WHERE clause.
--
:hehe:
July 21, 2009 at 2:57 pm
Slick84 (7/21/2009)
Also, from what I've read and learned and experienced, it would be better to use Inner Joins rather than joining within the WHERE clause.
It all depends on the data (and indexes and...). I agree that on larger data sets outer joins perform better than NOT EXISTS or NOT IN, but about the only way to know is to profile the difference and see what happens. Additionaly, I find it easuier to read, but that's me.. 🙂
To that end, here is an alternate method using LEFT OUTER JOIN: SELECT
b.oid,
b.sid,
b.rid
FROM
#b AS b
LEFT OUTER JOIN
a
ON a.oid = b.oid
AND a.sid = b.sid
AND a.rid = b.rid
WHERE
a.oid IS NULL
July 21, 2009 at 3:11 pm
Thats much better.
The recommended ANSI SQL standard is better to implement within the TSQL code to stay future-proof while also increasing your knowledge of joins which are an integral part of language.
--
:hehe:
July 22, 2009 at 4:08 am
Slick,
Yes it makes more sense to use Outer. Thank you SSC Journeyman for your help.
Ta
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply