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 2008 Forums
 Transact-SQL (2008)
 Need Help Join and match

Author  Topic 

sqlberry
Starting Member

6 Posts

Posted - 2013-07-22 : 09:09:52
Hi,
I need the following requirement in the table

http://s1350.photobucket.com/user/appleberry12/media/SQLreq2_zps4fb3bf84.png.html#/user/appleberry12/media/SQLreq2_zps4fb3bf84.png.html?&_suid=1374498536370019012751610889356

Can 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 is
select * from t1 join t2 on t1.col1=t2.col3 and t1.col2=t2.col2 and t1.col1=t2.col3
Go to Top of Page

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 is
select * 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 11:54:10
The solutions will be like this

1.
SELECT *
FROM Table1 t1
WHERE NOT EXISTS (SELECT 1
FROM Table2
WHERE Col1 = t1.Col1
AND Col2 = t1.Col2
)


2.
SELECT *
FROM Table1 t1
WHERE EXISTS (SELECT 1
FROM Table2
WHERE Col1 = t1.Col2
AND Col2 = t1.Col1
)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlberry
Starting Member

6 Posts

Posted - 2013-07-22 : 15:46:03
quote:
Originally posted by visakh16

The solutions will be like this

1.
SELECT *
FROM Table1 t1
WHERE NOT EXISTS (SELECT 1
FROM Table2
WHERE Col1 = t1.Col1
AND Col2 = t1.Col2
)


2.
SELECT *
FROM Table1 t1
WHERE EXISTS (SELECT 1
FROM Table2
WHERE Col1 = t1.Col2
AND Col2 = t1.Col1
)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 01:00:55
you're welcome
yep...JOIN is another way of doing the same thing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -