Solved

Return all records from two tables

Posted on 2013-06-12
4
365 Views
Last Modified: 2013-08-02
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
Comment
Question by:tolvudeild-VS
4 Comments
 
LVL 5

Accepted Solution

by:
DOSLover earned 105 total points
ID: 39242663
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
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 105 total points
ID: 39242667
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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question