CODEWITHSUNDEEP
sql-server-2005join
Shivkumar Deshmukh
Shivkumar Deshmukh

Reputation: 1148

Join two different columns from two different tables

i have two temporary table Table 1

 ID1         Name        ID2            Single
----------------------------------------------------
 1            ABC         1            100
 2            DEF         1            200

Table 2

 ID1         Name        ID2           Monthly
----------------------------------------------------
 3            PQR         2            500
 4            LMN         2            600

I want Output

 ID1         Name        ID2            Single   Monthly
--------------------------------------------------------
 1            ABC         1            100        NULL
 2            DEF         1            200        NULL
 3            PQR         2            NULL       500
 4            LMN         2            NULL       600

I used all Joins nothing working thanks in advance

Upvotes: 0

Views: 2784

Answers (5)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

You don't need JOINS, you need a UNION (ALL).

UNION (Transact-SQL)

Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

SQL Statement

SELECT ID1, Name, ID2, Single, NULL as Monthly
FROM   Table1
UNION ALL
SELECT ID1, Name, ID2, Null, Monthly
FROM   Table2

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

Just in case, for some mad reason, you really do need it as a JOIN rather than (as other's have pointed out) a UNION ALL:

create table #T1 (ID1 int,Name varchar(10),ID2 int,Single int)
insert into #T1 (ID1 ,        Name  ,      ID2    ,        Single)
select 1            ,'ABC',         1  ,          100 union all
select 2            ,'DEF',         1   ,         200

create table #T2 (ID1 int,Name varchar(10),ID2 int,Monthly int)
insert into #T2 (ID1 ,        Name  ,      ID2    ,        Monthly)
select 3            ,'PQR',         2   ,         500 union all
select 4            ,'LMN',         2   ,         600

select COALESCE(t1.ID1,t2.ID1) as ID1,COALESCE(t1.Name,t2.Name) as Name,
COALESCE(t1.ID2,T2.ID2) as ID2,t1.Single,t2.Monthly
from #T1 t1 full outer join #T2 t2 on 1=0

Gives the result you asked for

Upvotes: 2

Venkateswarlu Avula
Venkateswarlu Avula

Reputation: 441

Please use the following query...

Select ID1, Name, ID2, Single, NULL AS 'Monthly"

from Table1

Union

Select ID1, Name, ID2, NULL AS 'Single', Monthly

from Table2

Upvotes: 0

Ginka
Ginka

Reputation: 570

try to use UNION

SELECT * FROM Single
UNION
SELECT * FROM Monthly

Hope this helps.

Upvotes: 0

codeling
codeling

Reputation: 11379

JOIN won't work for that case, you need to use UNION here:

SELECT ID1, NAME, ID2, Single, NULL FROM Table1
UNION ALL
SELECT ID1, NAME, ID2, NULL, Monthly FROM Table2

Upvotes: 2

Related Questions