Join Multiple Select queries with where clauses for each

Question

So I have a series of Amortization schedules, and I'd like to run a query to return the balances for the month of May on each schedule

My query right now, looks something like this

select ace.date, ace.balance, aacs.date, aacs.balance, 
axl.date, axl.balance, la.date, la.balance, 
mrh.date, mrh.balance
from 
(select *
from [Entity1]
where MONTH (date) = 05 AND YEAR (date) = 2017) as ACE
full join
(select *
from [Entity2]
where MONTH (date) = 05 AND YEAR (date) = 2017) as AACS
on ACE.GLCredited = AACS.GLCredited
full join 
(select *
from [Entity3]
where MONTH (date) = 05 AND YEAR (date) = 2017) as AXL
on AXL.GLCredited = AACS.GLCredited
full join
(select *
from [Entity4]
where MONTH (date) = 05 AND YEAR (date) = 2017) as LA
on LA.GLCredited = AXL.GLCredited 
full join 
(select *
from [Entity5]
where MONTH (date) = 05 AND YEAR (date) = 2017) as MRH
on MRH.GLCredited = LA.GLCredited

this is what it returns

    date     |  balance     |    date      |    balance   |    date      |    balance   |    date      |    balance   |    date      |    balance    | 
------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------  |
NULL         | NULL         | 2017-05-31   | 563275.00    | NULL         | NULL         | NULL         | NULL         | NULL         | NULL          |
------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------  |
2017-05-31   | 896337.00    | NULL         | NULL         | NULL         | NULL         | NULL         | NULL         | NULL         | NULL          |
------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------  |
NULL         | NULL         | NULL         | NULL         | 2017-05-31   | 3746167.00   | NULL         | NULL         | NULL         | NULL          |
------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------  |
NULL         | NULL         | NULL         | NULL         | NULL         | NULL         | 2017-05-01   | 474774.00    | NULL         | NULL          |
------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------  |
NULL         | NULL         | NULL         | NULL         | NULL         | NULL         | NULL         | NULL         | 2017-05-31   | 127987.00     |

Is there any way that you know of, that will return the data without the null values?

Picture of database

SELECT column_names FROM table_name WHERE column_name IS NOT NULL; — Tarzan, Jun 21 '17 at 19:59
You need to specify precisely what you want the output to look like in terms of the input. All you've given us is a vague summary and a query that doesn't do what you want and an example of its output. We can't read your mind. Please also read & act on [mcve]. Also please use text whever possible--like instead of the that link. — philipxy, Jun 22 '17 at 09:45
I said I needed a query that would return the data without the null values... perhaps you overlooked. I believe that's pretty specific... if you can't help I'd prefer that you refrain from criticizing, especially if what you're asking me to do is right in the post. — Danielle, Jun 22 '17 at 13:41

score 1 · Answer 1 · answered Jun 21 '17 at 19:58

1

You would do this with a union not a join. Something like:

select date, balance
from [Entity1]
where MONTH (date) = 05 AND YEAR (date) = 2017) as ACE
union
select date, balance
from [Entity2]
where MONTH (date) = 05 AND YEAR (date) = 2017) as AACS
on ACE.GLCredited = AACS.GLCredited
union
select date, balance
from [Entity3]
where MONTH (date) = 05 AND YEAR (date) = 2017) as AXL
on AXL.GLCredited = AACS.GLCredited
union
select date, balance
from [Entity4]
where MONTH (date) = 05 AND YEAR (date) = 2017) as LA
on LA.GLCredited = AXL.GLCredited 
union
select date, balance
from [Entity5]
where MONTH (date) = 05 AND YEAR (date) = 2017) as MRH
on MRH.GLCredited = LA.GLCredited

answered Jun 21 '17 at 19:58

MikeS

1,734
1
9
13

Thats not working either as you typed it, the aliases were throwing it off. Once I corrected it, it shows a list of dates and balances which is better, but still doesnt help me identify the entity that the balance belongs to – Danielle Jun 21 '17 at 20:37
Just add a static value column to each select like : – MikeS Jun 22 '17 at 12:57
I thought of that last night and it's perfect. This is great. Thanks for your help Mike! – Danielle Jun 22 '17 at 13:42

score 1 · Answer 2 · answered Jun 21 '17 at 20:09

You could use a UNION:

Select * 
FROM
(
select 'ACE' as ScheduleName, date, balance from [Entity1]
union
select 'AACS' as ScheduleName, date, balance from [Entity2]
union
select 'AXL' as ScheduleName, date, balance from [Entity3]
union
select 'LA' as ScheduleName, date, balance from [Entity4]
union
select 'MRH' as ScheduleName, date, balance from [Entity5]
)u
where MONTH (date) = 05 AND YEAR (date) = 2017

Isaiah3015 · Answer 3 · 2017-06-21T20:20:24.893

0

You can easily just add IS NOT NULL on all your sub queries. Therefore the returns that you get from your sub queries will not include NULL

  ....
  from 
   from [Entity1]
   where MONTH (date) = 05 AND YEAR (date) = 2017 and *DATE is not null*) as ACE
  join
 (select *
  from [Entity2]
   where MONTH (date) = 05 AND YEAR (date) = 2017 *and BALANCE is not null*) as AACS
  on ACE.GLCredited = AACS.GLCredited
  join

edited Jun 21 '17 at 20:20

answered Jun 21 '17 at 20:08

Isaiah3015

493
3
11

Tried that. Returning the same results as above, surprisingly. – Danielle Jun 21 '17 at 20:11
Did you try just doing a JOIN and not a FULL JOIN? If you run just 1 of the subqueries with DATE IS NOT NULL, does it still return with a NULL ? – Isaiah3015 Jun 21 '17 at 20:21
When I run query as JOIN instead of FULL JOIN I get zero results I also tried to run just 1 of the subqueries as you suggested, the result set does not change – Danielle Jun 21 '17 at 20:31
Something is not right. The sub query should not return a NULL if written correctly. Would you mind posting a sample of your database that you are working with? – Isaiah3015 Jun 21 '17 at 20:55
Sure I just put a link to the photo in the original post – Danielle Jun 21 '17 at 22:34

Join Multiple Select queries with where clauses for each

3 Answers3