| Author |
 Topic  |
|
|
rbh123456789
Starting Member
15 Posts |
 Posted - 05/16/2012 : 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_OrderDate
There 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
USA
15568 Posts |
Posted - 05/16/2012 : 11:00:43
|
SELECT CompanyID, OrderDate FROM View1
EXCEPT
SELECT CompanyID, OrderDate FROM View2 |
 |
|
|
rbh123456789
Starting Member
15 Posts |
Posted - 05/16/2012 : 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
USA
15568 Posts |
Posted - 05/16/2012 : 12:08:00
|
SELECT CompanyID, OrderDate, column3 FROM View1 A
WHERE NOT EXISTS(SELECT * FROM View2 WHERE CompanyID=A.CompanyID AND OrderDate=A.OrderDate) |
|
|
|
rbh123456789
Starting Member
15 Posts |
Posted - 05/16/2012 : 12:26:28
|
| robvolk, THANK YOU! this query actually runs a lot faster too. thanks again. |
|
|
| |
Topic |
|
comparing 2 views
