CODEWITHSUNDEEP
sql-server
Cory
Cory

Reputation: 7

Merging columns from 2 tables to a single table in MS SQL 2014

I used this link to help me get to where I am, but now my output is wrong.

I have 2 columns, ColumnA from TableA, and ColumnB from Table B. I want TableC consiting of 2 columns, ColumnA and ColumnB. MatchA and MatchB are identical. MatchA is in TableA, MatchB is in TableB. @InputVal is how the values are selected from ColumnA + B. Here is what I have done:

SELECT ColumnA, ColumnB
FROM TableA, TableB
WHERE MatchA=MatchB
AND MatchA=@InputVal

This produces the output:

ColumnA   ColumnB
44        Test1
45        Test1
46        Test1
44        Test2
45        Test2
46        Test2
44        Test3
45        Test3 
46        Test3

When, logically, it should be:

ColumnA   ColumnB
44        Test1
45        Test2
46        Test3

How can I correct my logic to fix the flaw? I've run through many variations of the statement construction and I can't seem to wrap my head around it. Thanks.

EDIT: User snow_FFFFFF had the solution right, but was missing a part only because I left out a detail. 

SELECT TableA.ColumnA, TableB.ColumnB
FROM TableA INNER JOIN TableB ON TableA.MatchA = TableB.MatchB
AND TableA.SecondMatchA=TableB.SecondMatchB --The change needed

Upvotes: 0

Views: 223

Answers (2)

Dotnetter
Dotnetter

Reputation: 261

Try to use UNION SELECT To Merge Columns From two Tables as below:

SELECT TableB.ColumnB FROM TableB
UNION ALL
SELECT TableA.ColumnA FROM TableA WHERE TableA.MatchA = @InputVal

Upvotes: 0

snow_FFFFFF
snow_FFFFFF

Reputation: 3301

I think you want to join your tables:

SELECT TableA.ColumnA, TableB.ColumnB
FROM TableA INNER JOIN TableB ON TableA.MatchA = TableB.MatchB

The INNER JOIN will only return rows from each where there is a match. A LEFT OUTER JOIN (instead of the INNER JOIN) would give you all rows from TableA and values from TableB where the join was successful.

Right now, you are performing a CROSS JOIN by using the comma between the tables.

Upvotes: 1

Related Questions