Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • 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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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