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.
Author |
Topic |
sqlberry
Starting Member
6 Posts |
Posted - 2013-07-22 : 09:09:52
|
Hi,I need the following requirement in the tablehttp://s1350.photobucket.com/user/appleberry12/media/SQLreq2_zps4fb3bf84.png.html#/user/appleberry12/media/SQLreq2_zps4fb3bf84.png.html?&_suid=1374498536370019012751610889356Can I get help?TIA |
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-07-22 : 09:28:23
|
select * from t1 left outer join t2 on t1.col1=t2.col1 and t1.col2=t2.col2 and t1.col3=t2.col3 where t1.col1 is null(might have to check some more or all columns for null depending on how nullable they are)second one isselect * from t1 join t2 on t1.col1=t2.col3 and t1.col2=t2.col2 and t1.col1=t2.col3 |
|
|
sqlberry
Starting Member
6 Posts |
Posted - 2013-07-22 : 10:40:15
|
quote: Originally posted by LoztInSpace select * from t1 left outer join t2 on t1.col1=t2.col1 and t1.col2=t2.col2 and t1.col3=t2.col3 where t1.col1 is null(might have to check some more or all columns for null depending on how nullable they are)second one isselect * from t1 join t2 on t1.col1=t2.col3 and t1.col2=t2.col2 and t1.col1=t2.col3
I do not have col3 in T2. Please help understand |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 11:54:10
|
The solutions will be like this1. SELECT *FROM Table1 t1WHERE NOT EXISTS (SELECT 1FROM Table2WHERE Col1 = t1.Col1AND Col2 = t1.Col2)2.SELECT *FROM Table1 t1WHERE EXISTS (SELECT 1FROM Table2WHERE Col1 = t1.Col2AND Col2 = t1.Col1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sqlberry
Starting Member
6 Posts |
Posted - 2013-07-22 : 15:46:03
|
quote: Originally posted by visakh16 The solutions will be like this1. SELECT *FROM Table1 t1WHERE NOT EXISTS (SELECT 1FROM Table2WHERE Col1 = t1.Col1AND Col2 = t1.Col2)2.SELECT *FROM Table1 t1WHERE EXISTS (SELECT 1FROM Table2WHERE Col1 = t1.Col2AND Col2 = t1.Col1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Thank you so much. I tried JOIN too and got the same result. So I learnt that my query is working.Thanks a lot for helping newbies. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-23 : 01:00:55
|
you're welcomeyep...JOIN is another way of doing the same thing------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|