May 20, 2008 at 4:25 am
Hi Friends,
I have a requirements like this, checking Multiple Column data with another table columns data.
I mean to say, I have a Table as Employee and columns are
EmpID, EmpName & Pin. I have to Check all three combination with the 2nd table and find out the different between these two tables.
I was using (Not In) to find out difference as per EmpID, but How can I achieve this?
e.i:
Select * from Table1
Where EmpId Not In (Select EmpID from table2)
Now 3nos. combination I have to check with the other table
EmpId, EmpName and Pin
Can you tell me how can i achieve this?
Cheers!
Sandy.
--
May 20, 2008 at 4:50 am
Hi Sandy,
Outer joins will give you what you need probably:
create table table1(a int, b int, c int)
create table table2(a int, b int, c int)
go
insert table1(a, b, c)
values (1, 1, 1)
insert table1(a, b, c)
values (1, 2, 1)
insert table1(a, b, c)
values (1, 3, 2)
insert table2(a, b, c)
values (1, 0, 1)
insert table2(a, b, c)
values (1, 2, 1)
insert table2(a, b, c)
values (1, 3, 1)
go
print 'common part'
select t1.a, t1.b, t1.c from table1 t1 inner join table2 t2
on t1.a = t2.a and t1.b = t2.b and t1.c = t2.c
print 'exist in t1, not in t2'
select t1.a, t1.b, t1.c from table1 t1 left join table2 t2
on t1.a = t2.a and t1.b = t2.b and t1.c = t2.c
where t2.a is null
print 'exist in t2, not in t1'
select t2.a, t2.b, t2.c from table1 t1 right join table2 t2
on t1.a = t2.a and t1.b = t2.b and t1.c = t2.c
where t1.a is null
go
drop table table1
drop table table2
go
Regards
Piotr
...and your only reply is slàinte mhath
May 20, 2008 at 4:57 am
hey Piotr,
I got a new solution and also interesting, I am not sure It is correct or Not but Still its working fine for me...
Only one condition works fine for me.
Cheers!
Sandy.
--
May 20, 2008 at 5:01 am
Not sure what you want to say - is it working or not? if you join your tables on all three fields and use outer join AND you will check if relevant column from opposite table is null, you will get all records that do not match.
Piotr
...and your only reply is slàinte mhath
May 20, 2008 at 5:07 am
Piotr,
I mean to say, I got quick another Solution for this,
But your query is working fine, No issue on that but it may raise a performance issue if the table is large.
Cheers!
Sandy.
--
May 20, 2008 at 6:22 am
Sandy (5/20/2008)
But your query is working fine, No issue on that but it may raise a performance issue if the table is large.
Not if it's working off a good set of indexes. If it's just three random columns, you're right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2008 at 6:36 am
Grant,
Then what you wants to say?
I mean Do you have any other Idea?
Please tell me then?
I have used a quick Idea to perform this...
Cheers!
Sandy.
--
May 20, 2008 at 6:39 am
Sorry I wasn't clear. I agreed with the outer join approach, assuming your indexes support it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2008 at 6:41 am
How did you do it Sandy? What is your solution?
I believe Grant wanted to say that if these three columns are indexed on both tables, outer join approach will perform very well for big tables as well.
I used to run such queries on tables containing 50k of (usually nonindexed) rows and performance was satisfactory.
Cheers
Piotr
...and your only reply is slàinte mhath
May 20, 2008 at 6:48 am
If it's just three random columns, you're right.
Piotr,
If it's just three random columns, If Not Then....My Question Starts here??
Got it?
My Solution is very simple...and single line of code..and easy to use too..
Cheers!
Sandy.
--
May 20, 2008 at 6:50 am
Well, don't be stingy, show your solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2008 at 7:01 am
Now See here....
select * from #temp
where a+b+c in (select a1+b1+c1 from #temp1)
e.i: replace In with .... [in/not in/ ] ........
he he he...lolz
It will work for Int as well as for varchar datatypes.
You can try it.
Cheers!
Sandy.
--
May 20, 2008 at 7:04 am
create table #temp
(
a int,
b int,
c int
)
-------------------------
Insert into #temp
select 1,1,1
union all
select 1,2,2
union all
select 1,2,1
-----------------------
select * from #temp
----------------------
create table #temp1
(
a1 int,
b1 int,
c1 int
)
-------------------------
Insert into #temp1
select 1,1,1
union all
select 1,2,2
union all
select 1,2,3
-----------------------
select * from #temp1
----------------
select * from #temp
where a+b+c in (select a1+b1+c1 from #temp1)
---------------
drop table #temp
---------------
drop table #temp1
Cheers!
Sandy.
--
May 20, 2008 at 7:10 am
Now this works but this is going to perform poorly always for big tables. There is no way this query can use any indexes.
Piotr
...and your only reply is slàinte mhath
May 20, 2008 at 7:18 am
Yes,
But if you try, you can also achieve, but you have to work a little more.
but As I told Its One line of code and Easy to use and Quicker also.
That's the different...:w00t:
it works fine in Pre- Prod server having data 2,72, 143 records.
Hope it'll work fine.
Cheers!
Sandy.
--
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply