• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Return all records from two tables

Hi.

I have two tables with fields
date, member, value
In the two tables some records mach on member and I subtract the values but there could be a member i one table but not the other and then I want to get that record as well and the value.

Is this possible?

My current sql is like this (not returning all members)

 SELECT            DATE.a, trade_mbr.a, GA7VAL.a-GA7VAL.b,
 FROM               clearbuy a, clearsell b
 INNER JOIN        trade_mbr.a=trade_mbr.b
 ORDER BY        member.a Asc

Best regards
Oli
0
tolvudeild-VS
Asked:
tolvudeild-VS
2 Solutions
 
DOSLoverCommented:
I would use a FULL OUTER JOIN to pull records from both, and COALESCE to select a non-null value as follows:
 SELECT COALESCE(a.trade_mbr,b.trade_mbr), a.DATE., b.date, COALESCE(a.GA7VAL,0)- COALESCE(b.GA7VAL) as ValueDiff
   FROM clearbuy a
   FULL OUTER JOIN clearsell b ON a.trade_mbr=b.trade_mbr
 ORDER BY  1 

Open in new window

0
 
momi_sabagCommented:
you want something like this:

with a as (
SELECT            a.date date1, b.date date2, a.trade_mbr mbr_a, b.trade_mbr mbrb,
   a.GA7VAL vala, b.GA7VAL valb
 FROM               clearbuy a
 full outer JOIN    clearsell b on    a.trade_mbr =b.trade_mbr
 ORDER BY        member.a Asc
)
select date1, mbr_a, vala from a where mbrb is null
union all
select date2, mbr_b, valb from a where mbra is null
union all
select date1, mbr_a, vala - valb from a where mbra is not null and mbrb is not null
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now