0

Hi all,
i hope anyone of you can help me on the following matter:
I have two tables:

Recordingid Connection Sequenceno
1233036621 1 3
1233036621 1 4
1233036621 2 8
1233036621 2 9

And the second one:
Recordingid Connection Sequenceno Code Cause
1233036621 1 1 21 9
1233036621 1 2 22 0
1233036621 1 5 23
1233036621 2 6 21 9
1233036621 2 7 22 0
1233036621 2 10 23

What i need is to have this as a result

Recordingid Connection Sequenceno Code Cause
1233036621 1 1 21 9
1233036621 1 2 22 0
1233036621 1 3
1233036621 1 4
1233036621 1 5 23
1233036621 2 6 21 9
1233036621 2 7 22 0
1233036621 2 8
1233036621 2 9
1233036621 2 10 23

(just to clarify: this is simply union between the two tables, but as from the second one I need to take only 3 columns and from the first one 5 columns, so UNION of two separate queries does notwork / it needs the same number of columns in both queries/)

Could any one give some ideas?

Thank you in advance,
milenio

3
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by Ezzaral
0

I beleive you need to use a full outer join with a coelesce on the 3 keys to get your desired result.

SELECT coalesce(a.Recordingid,b.recordingid), coalesce(a.Connection,b.Connection), coalesce(a.Sequenceno,b.Sequenceno), b.Code, b.Cause
FROM dbo.Table_1 a 
Full OUTER JOIN dbo.Table_2 b on a.recordingid = b.recordingid AND a.connection = b.connection AND a.Sequenceno = b.Sequenceno

Edited by cgyrob: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.