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 |
|
rbh123456789
Starting Member
15 Posts |
 Posted - 2012-05-16 : 10:42:40
|
| Hey guys,I have two views.View1: A list of Orders that must be resolved. Fields are CompanyID (not unique) and OrderDate (unique only when the CompanyID is involved).View2: A list of Completed Orders. Fields are CompanyID and Original_OrderDateThere is no 'orderID' to link between the two views. However, there is only ever 1 order per day per Company.Basically, I am trying to find out which Orders are Outstanding (eg. View1 is missing its match in View2).So i need to be able to tell SQL to look at View1, then match up the CompanyIDs, and only show the View2 entries that do NOT have a matching OrderDate.Any help would be appreciated. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-16 : 11:00:43
|
| SELECT CompanyID, OrderDate FROM View1EXCEPTSELECT CompanyID, OrderDate FROM View2 |
 |
|
|
rbh123456789
Starting Member
15 Posts |
Posted - 2012-05-16 : 12:06:17
|
| Thanks robvolk. one more question. i have another field in view1, which doesn't exist in view2, that i want to display in the results.how do i achieve that? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-16 : 12:08:00
|
| SELECT CompanyID, OrderDate, column3 FROM View1 AWHERE NOT EXISTS(SELECT * FROM View2 WHERE CompanyID=A.CompanyID AND OrderDate=A.OrderDate) |
|
|
|
rbh123456789
Starting Member
15 Posts |
Posted - 2012-05-16 : 12:26:28
|
| robvolk, THANK YOU! this query actually runs a lot faster too. thanks again. |
|
|
|
|
|
|
comparing 2 views