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

Just add placeholder columns on the smaller query, such as "null as code" or "0 as code".

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

Edit: Nevermind, I misread.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.