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 |
|
Steel_m
Starting Member
25 Posts |
 Posted - 2008-09-11 : 03:10:01
|
| Hi all. Need some help comparing two tables in ql 7.0. They both "should have the same records stored but somtimes 1 has errors that take a long time to identify manually. So how can i compare these tables to produce the records that do not match anything. eg...T1 T2id fruit owner id fruity person001 grape kim 001 grape kim001 apple ken 001 AAAMPLE ken001 lime dean 001 lime dean 001 lemon lisa 001 lemon MIZ Would like it to output 001 AAAMPLE ken 001 lemon MIZ |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-11 : 06:18:36
|
| Select t2.* from table2 as t2 where no texists(select * from table1 where id=t2.id and fruit=t2.fruit and owner=t2.owner)MadhivananFailing to plan is Planning to fail |
 |
|
|
Steel_m
Starting Member
25 Posts |
Posted - 2008-09-11 : 10:20:15
|
[quote]Originally posted by madhivanan
Select t2.* from table2 as t2 where no texists(select * from table1 where id=t2.id and fruit=t2.fruit and owner=t2.owner)Thanks Madhivanan. I Tried as you adviced but i recieved a error message (Incorrect syntax near the keyword 'select') line 2 The code i have is Select t2.* from t2 as t2 where notexists select * from t1 where id=t2.id and fruit=t2.fruit and owner=t2.ownerDo you see where i went wrong ? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-11 : 10:30:20
|
quote:
Originally posted by Steel_m
[quote]Originally posted by madhivanan
Select t2.* from table2 as t2 where no texists(select * from table1 where id=t2.id and fruit=t2.fruit and owner=t2.owner)Thanks Madhivanan. I Tried as you adviced but i recieved a error message (Incorrect syntax near the keyword 'select') line 2 The code i have is Select t2.* from t2 as t2 where notexists select * from t1 where id=t2.id and fruit=t2.fruit and owner=t2.ownerDo you see where i went wrong ?
Why did you remove the braces?Select t2.* from t2 as t2 where not exists (select * from t1 where id=t2.id and fruit=t2.fruit and owner=t2.owner)MadhivananFailing to plan is Planning to fail |
|
|
|
Steel_m
Starting Member
25 Posts |
Posted - 2008-09-11 : 11:36:04
|
Thanks alot Madhivanan, it worked fine  . I simply inserted the other columns i needed and it was complete.Kindest Regards |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-12 : 02:24:41
|
quote:
Originally posted by Steel_m
Thanks alot Madhivanan, it worked fine . I simply inserted the other columns i needed and it was complete.Kindest Regards
You are welcome  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
comparing tables