March 17, 2012 at 11:41 pm
Dear all,
table2 has one to many rel with table1 and table3
table1 (table1Id int,table2Id int,col3 int)
table2(table2Id int,col2 int,col3 int)
table3(table3Id,table2Id int,col3 int)
--are both correct if yes then which one is better? i checked both are good as long as join is used.
1) select t1.col1,ti.col2
from table1 t1 join table2 t2 on t1.table2Id =t2.table2Id
where not exists
(select t3.table3Id
from table3 t3 join table2 t22 on t3.table2Id =t22.table2Id and t1.col3=t3.col3 and t2.table2Id =t22.table2Id )
or
select t1.col1,ti.col2
from table1 t1 join table2 t2 on t1.table2Id =t2.table2Id
where not exists
(select t3.table3Id
from table3 t3 join table2 t22 on t3.table2Id =t22.table2Id
where t1.col3=t3.col3 and t2.table2Id =t22.table2Id )
yours sincerely
March 18, 2012 at 12:35 am
March 18, 2012 at 11:05 pm
Check following blog post to get differences b/w IN, NOT IN and EXISTS.
Link: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/%5B/url%5D
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply