SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Help Join and match
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlberry
Starting Member

6 Posts

Posted - 07/22/2013 :  09:09:52  Show Profile  Reply with Quote
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 - 07/22/2013 :  09:28:23  Show Profile  Reply with Quote
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 - 07/22/2013 :  10:40:15  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/22/2013 :  11:54:10  Show Profile  Reply with Quote
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 - 07/22/2013 :  15:46:03  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/23/2013 :  01:00:55  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000