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 |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2011-05-19 : 08:33:46
|
Can someone please help?? I have two tables - table 1 is invoices from December 2010 and table 2 is invoices from January 2011. I want the records from table 2 that are not in table 1. How is that accomplished |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-19 : 08:35:32
|
| You can use NOT IN, LEFT JOIN, EXCEPT or NOT EXISTS.JimEveryday I learn something that somebody else already knew |
 |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-05-19 : 08:37:56
|
| Thanks, Jim. I tried left join but it did not work. I must have had incorrect syntax, how do you use it? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-19 : 08:44:53
|
| Correctly. Show us how you used it and we can correct it.JimEveryday I learn something that somebody else already knew |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-05-19 : 08:50:18
|
| OK, here it is:select distinct memberidinto ##tempper1 from membermonthswhere year(reportingmonth) = '2010' and month(reportingmonth) = '12' and sponsorid not in ('100002', '100003', '100360')select memberid , subscriberid , sponsorid , sponsorname , ratecodeinto ##tempper2from membermonthswhere year(reportingmonth) = '2011' and month(reportingmonth) = '1' and sponsorid not in ('100002', '100003', '100360')select p1.memberid , p2.memberid , p2.subscriberid , p2.sponsorid , p2.sponsorname , p2.ratecodefrom ##tempper1 p1 left join ##tempper2 p2 on p1.memberid = p2.memberidCan you also tell me how to use NOT IN please?? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-19 : 08:55:38
|
| select p1.memberid, p2.memberid, p2.subscriberid, p2.sponsorid, p2.sponsorname, p2.ratecodefrom ##tempper1 p1 left join ##tempper2 p2 on p1.memberid = p2.memberidwhere p2.memberid is nullJimEveryday I learn something that somebody else already knew |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2011-05-19 : 08:57:27
|
| OH man, that was easy! Thanks Jim!! Have a nice day..... |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-19 : 09:41:34
|
| No prob. NOT EXISTS may be even faster and better if your data set gets big (i.e. > a few 100k rows)JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Matching Query