johnnyg123
asked on
Table join question
Here is some sample data from 2 sql server 2008 tables
CustomerOrderNew
CustomerID OrderDate OrderAmount
1 04/01/2018 400
2 04/01/2018 666
3 04/20/2018 787
CustomerOrderOld
CustomerID OrderDate OrderAmount
1 04/01/2018 250
1 04/11/2018 45
5 05/01/2018 1000
Trying to write a query that will return rows from both tables. The “catch” is if there is an entry in CustomerOrderOld with a matching customerid and orderdate in CustomerOrderNew, I do not want to return it
Given the sample data above I would like the output to be
CustomerID OrderDate OrderAmount
1 04/01/2018 400
2 04/01/2018 666
1 04/11/2018 45
3 04/20/2018 787
5 05/01/2018 1000
CustomerOrderNew
CustomerID OrderDate OrderAmount
1 04/01/2018 400
2 04/01/2018 666
3 04/20/2018 787
CustomerOrderOld
CustomerID OrderDate OrderAmount
1 04/01/2018 250
1 04/11/2018 45
5 05/01/2018 1000
Trying to write a query that will return rows from both tables. The “catch” is if there is an entry in CustomerOrderOld with a matching customerid and orderdate in CustomerOrderNew, I do not want to return it
Given the sample data above I would like the output to be
CustomerID OrderDate OrderAmount
1 04/01/2018 400
2 04/01/2018 666
1 04/11/2018 45
3 04/20/2018 787
5 05/01/2018 1000
ASKER
hey scott
Thanks so much for the post!
I get the following
CustomerID OrderDate OrderAmount
1 2018-04-01 400
2 2018-04-01 666
3 2018-04-20 787
I reread my question and I apologize for not making it clear that I want the unmatched entries from customerorderold as well
just wanted to skip any matching entries with same customerid and orderdate
Given the sample data above I would like the output to be
CustomerID OrderDate OrderAmount
1 04/01/2018 400
2 04/01/2018 666
1 04/11/2018 45
3 04/20/2018 787
5 05/01/2018 1000
Thanks!
Thanks so much for the post!
I get the following
CustomerID OrderDate OrderAmount
1 2018-04-01 400
2 2018-04-01 666
3 2018-04-20 787
I reread my question and I apologize for not making it clear that I want the unmatched entries from customerorderold as well
just wanted to skip any matching entries with same customerid and orderdate
Given the sample data above I would like the output to be
CustomerID OrderDate OrderAmount
1 04/01/2018 400
2 04/01/2018 666
1 04/11/2018 45
3 04/20/2018 787
5 05/01/2018 1000
Thanks!
That's what I was trying to do. Without any usable sample data, I wasn't able to test my code. Us experts are already volunteering our time to help you, it seems a bit much that we also have to build the test data too(!). But I'll go ahead and create usable data myself:
CREATE TABLE #CustomerOrderNew (
CustomerID int NOT NULL,
OrderDate date NOT NULL,
OrderAmount int NOT NULL
);
INSERT INTO #CustomerOrderNew VALUES
(1, '04/01/2018', 400),
(2 , '04/01/2018', 666),
(3 , '04/20/2018', 787)
CREATE TABLE #CustomerOrderOld (
CustomerID int NOT NULL,
OrderDate date NOT NULL,
OrderAmount int NOT NULL
);
INSERT INTO #CustomerOrderOld VALUES
(1 , '04/01/2018' , 250),
(1 , '04/11/2018', 45),
(5 , '05/01/2018', 1000)
CREATE TABLE #CustomerOrderNew (
CustomerID int NOT NULL,
OrderDate date NOT NULL,
OrderAmount int NOT NULL
);
INSERT INTO #CustomerOrderNew VALUES
(1, '04/01/2018', 400),
(2 , '04/01/2018', 666),
(3 , '04/20/2018', 787)
CREATE TABLE #CustomerOrderOld (
CustomerID int NOT NULL,
OrderDate date NOT NULL,
OrderAmount int NOT NULL
);
INSERT INTO #CustomerOrderOld VALUES
(1 , '04/01/2018' , 250),
(1 , '04/11/2018', 45),
(5 , '05/01/2018', 1000)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try
select * from customerordernew new
Where not exists(select * from customerorderold old
Where old.customerid=new.customerid
And old.orderdate=new. orderdate)
ASKER
Thanks Scott! Appreciate the help
Point taken on the data. my bad
Point taken on the data. my bad
FROM CustomerOrderNew
UNION ALL
SELECT CustomerID, OrderDate, OrderAmount
FROM CustomerOrderOld coo
WHERE NOT EXISTS(
SELECT 1
FROM CustomerOrderNew con
WHERE con.CustomerID = coo.CustomerID AND
con.OrderDate = con.OrderDate
)