CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
Shahsra
Shahsra

Reputation: 1009

Join two columns from two different tables

I have a problem in joining the two columns from two different tables.

The Scenario is: I have a table A with 11 columns and another table B with 6 columns.

There is a column names SAMPLE1 which exists in both the tables.But, SAMPLE2 from 1st table A and ABC from 2nd table B are having the same value but with different column names. Same with SAMPLE3 and DEF. Now I would like to join that columns to a single column (which consists of data from both the tables) and rest of the columns also should appear on that final table.

Example:

Table A

SAMPLE1    SAMPLE2    SAMPLE3 .........SAMPLE 11 (Total 11 columns in this table)
 
 US          75.2       US1_US           NULL

 INDIA       71         I3_INDIA         NULL

 UK          1851.23    UK1_UK           NULL

Table B

SAMPLE1      ABC         DEF............. XYZ (Total 6 columns in this table)

CHINA        123.2       C1_CHINA         2

JAPAN        1.1         J1_JAPAN         2

GERMANY      10.2314     G1_GERMANY       2

SINGAPORE    100.22      S1_SINGAPORE     2

Now I would like to see the output like this:

SomeTable

SAMPLE1      SOMENAME1    SOMENAME2..SAPMLE 11   ABC    DEF .....  SOMENAME3   
  
   US         75.2        US1_US      NULL       NULL    NULL      NULL

   INDIA      71          I3_INDIA    NULL       NULL    NULL      NULL 

   UK         1851.23     UK1_UK      NULL       NULL    NULL      NULL 

   CHINA      123.2       C1_CHINA    NULL       NULL    NULL       2   

   JAPAN      1.1         J1_JAPAN    NULL       NULL    NULL       2 

   GERMANY    10.2314     G1_GERMANY  NULL       NULL    NULL       2   

   SINGAPORE  100.22     S1_SINGAPORE NULL       NULL    NULL       2

In short:

SELECT (SAMPLE1(FROM TABLE A) + SAMPLE1(FROM TABLE B)) AS SAMPLE1, (SAMPLE2 + ABC) AS SOMENAME1, (SAMPLE3 + DEF) AS SOMENAME2, A.SAMPLE4, A.SAMPLE5,..., B.GHI, B.JKL,... (A.SAMPLE11 +B.XYZ) AS SOMENAME3

I have used union but it didn't work.

select SAMPLE1,SAMPLE2,SAMPLE3,...,SAMPLE 11 from TABLE A
UNION 
SELECT SAMPLE1, ABC, DEF, ...., XYZ FROM TABLE B

Now I am getting an error:

Msg 205, Level 16, State 1, Line 1

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I have used union, coalesce, full outer join (all the suggestions or answers below)

The final code will be used in a select statement. How?

Upvotes: 4

Views: 21640

Answers (3)

jack.mike.info
jack.mike.info

Reputation: 128

TRY THIS, IT MAY BE USEFUL

table 1 contains 5 columns

select col1,col2...col5 from table1

table 2 contains 3 columns

select col1,col2,col3 from table1

Query:

select col1,col2,col3,col4,col5 from table1
union
select col1,col2,col3,'','' from table1

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Join the two tables using FULL OUTER (preserve data from either table where it does not exist on the other), then use COALESCE to get the common SAMPLE1 column from whichever table has it.

SELECT COALESCE(A.SAMPLE1, B.SAMPLE1) SAMPLE1,
     A.SAMPLE2,
     A.SAMPLE3,
     ...
     A.SAMPLE11,
     B.ABC,
     B.DEF,
     ...
     B.XYZ
FROM table1 A FULL OUTER JOIN table2 B on A.SAMPLE1 = B.SAMPLE1

References: MSDN - Using Outer Joins / COALESCE

Upvotes: 2

Andomar
Andomar

Reputation: 238086

You're probably looking for a join, like:

select  a.sample1
,       a.sample2
,       b.abc
,       b.def
...  etc  ...
from    TableA as a
full outer join
        TableB as b
where   a.sample1 = b.sample1

Upvotes: 2

Related Questions