November 7, 2006 at 1:55 pm
I've got 2 datasets that look something like this.
T A B L E # 1 T A B L E # 2
Date Biller Amount / Date Biller Lines
10/1/06 Tammy $200 / NULL NULL NULL
NULL NULL NULL / 10/2/06 Lisa 123
10/3/06 Carla $300 / 10/3/06 Carla 45
What type of join would I do to join these 2 tables?
November 7, 2006 at 2:00 pm
How about
SELECT * FROM #1 UNION SELECT * FROM #2
November 7, 2006 at 2:02 pm
I guess i left off a critical piece of info... i'm using temp tables. I don't think UNION statements work on temp tables.
November 7, 2006 at 2:31 pm
I have tested the following snippet. It works.
CREATE TABLE #1 (col1 INT, col2 VARCHAR(1))
CREATE TABLE #2 (col1 INT, col2 VARCHAR(1))
INSERT INTO #1 VALUES (1, 'A')
INSERT INTO #2 VALUES (2, 'B')
SELECT * FROM #1
SELECT * FROM #2
SELECT * FROM #1 UNION SELECT * FROM #2
DROP TABLE #1
DROP TABLE #2
November 7, 2006 at 2:31 pm
Ya they do work, test the solution and if it doesn't work, then post the required result from that sample data and we'll get back to you.
November 7, 2006 at 2:42 pm
Actually, the UNION is going to stack the results, which is not what I want. I want 1 row per date. So my final result would look like:
10/1/2006 tammy $200 0
10/2/2006 Lisa 0 123
10/3/2006 carla $300 45
Make sense?
November 7, 2006 at 2:42 pm
Raymond, what do you mean when you say "join"?
_____________
Code for TallyGenerator
November 7, 2006 at 2:49 pm
looking back over what i posted i can see where it makes for some confusion. here is some sample data:
table1:
10/1/2006 / Tammy / $200
10/4/2006 / Lisa / $300
table2:
10/2/2006 / Carla / 123
10/3/2006 / Tammy / 45
10/4/2006 / Lisa / 38
Results:
10/1/2006 / Tammy / $200 / 0
10/2/2006 / Carla / $0 / 123
10/3/2006 / Tammy / $0 / 45
10/4/2006 / Lisa / $300 / 38
So my date is common field between the 2 tables. How can I join these into my desired results?
November 7, 2006 at 3:11 pm
Select ISNULL(T1.Date, T2.Date), ISNULL(T1.Biller, T2.Biller), ISNULL(T1.Amount, 0), ISNULL(T2.Lines, 0)
FROM Table1 T1
FULL OUTER JOIN Table2 T2 ON T1.Date = T2.Date and T1.Biller = T2.Biller
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply