Solved

Return all records from two tables

Posted on 2013-06-12
4
362 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now