CODEWITHSUNDEEP
sql-serverjoin
GLP
GLP

Reputation: 3675

how to outer join two tables?

I have following tables: tblA (id, price), tblB (id, minPay) For example:

tblA
id        price
001       1.00
003       2.00

tblB
id        minPay
001       10.00
004       20.00

I need to somehow join this two tables to get following result:

id        price       minPay
001       1.00        10.00
003       2.00        0
004       0           20.00

Does anyone know how to achieve this?

Upvotes: 0

Views: 61

Answers (5)

Ajay2707
Ajay2707

Reputation: 5798

Check this query

declare @tblA table (id varchar(50), price varchar(50))
insert into @tblA values ('001' ,'1.00'),('003','2.00')

declare @tblB table (id       int, minPay varchar(50))
insert into @tblB values ('001' ,'10.00'),('004','20.00')

select 
  case when b.id IS null then a.id else b.id end id , 
  case when b.minPay IS null then a.price else '0' end price, 
  isnull(minpay , 0) minpay
from @tblA a 
  full outer join @tblB b 
on A.id = b.id

Upvotes: 0

Joe Enos
Joe Enos

Reputation: 40393

A FULL OUTER JOIN will accomplish this, but I would assume you've got a master table which you could start with that contains all records, and then you're really better off starting there, then doing LEFT JOINs. For example, assuming you have an Items table which contains all IDs (and assuming these are 1-1 relationships and not 1-many), then:

select
    i.id
    ,isnull(a.price, 0) [price]
    ,isnull(b.minPay, 0) [minPay]
from Items i
left join tblA a on i.id = a.id
left join tblB b on i.id = b.id

Upvotes: 0

Horaciux
Horaciux

Reputation: 6477

Use COALESCE() to replace NULL with ZERO value

SELECT a.id, coalesce(a.price,0) AS Price, coalesce(b.minpay,0) as Minipay
FROM tblA a FULL OUTER JOIN tblB b 
ON a.id=b.id

Upvotes: 2

TheBlueMan
TheBlueMan

Reputation: 427

I think you are looking for a full outer join. http://www.w3schools.com/sql/sql_join_full.asp

SELECT tblA.id, tblA.price, tblB.minPay
FROM tblA
FULL OUTER JOIN tblB
ON tblA.id=tblB.id

Upvotes: 1

Jens
Jens

Reputation: 69440

This query should give you the expected result.

select tblA.id, price, minpay from tblA full outer join tblB on tblA-id=tblb.id

Upvotes: 0

Related Questions