Re: Can I do this in SQL? How?

The query that Rick proposed did not denote what [of which file] the dollar amount represented. Although an unstated requirement, that detail was conspicuous in the proposed report output, where each had its own column. Of course literals could easily be added in a column to denote that [i.e. which dollar amount] for each row.

The original requirement was stated as effectively "list\select all accounts with at least one contribution and one distribution". The query in the quoted text performs "list all accounts with ( at least one contribution ) AND ( at least one distribution )", not "list\select all accounts with ( at least one contribution ) AND ( one distribution )". Since the limited amount of sample output reflects both the latter as well as the former logic, which logic was desirable, is not obvious; presumably the former, such that it really "worked".

FWiW the first CTE is likely the same as an INTERSECT of the data in Acct# from the Cont & Dist tables; IMO having requested the intersection of the account number in those two tables would be clearer than selecting those rows from the parent which are IN the dependents. The second CTE is presumably an unnecessary copy of data, at least to obtain the originally described results; i.e. the FileID column exists only to provide a field on which to ORDER BY but omit from the result. Since the given sample output did not imply such an order, probably the order could be effected across the existing column Acct# for which no additional copy is required; the sample output did show ascending order of Acct#.

As to the columns matching in the UNION, generally I would both choose NULL versus a zero value and use CAST(NULL as DECIMAL(7, 2)) [or similar casting, to the desired data type\attributes] to effect the consistent type for the literal [or the NULL value] representing the non-existent CONTR$ and DIST$ values.

Regards, Chuck

Jack Prucha wrote:

Thanks Joe & Rick.

Joe, it worked perfectly after I put the comma back in after the
first CTE.

Joe Pluta wrote:

-- Get accounts with at least one of each
with T1 as (
select ACCT# from AcctTbl
where ACCT# in (select ACCT# from Conttbl)
and ACCT# in (select ACCT# from DistTbl)),

-- Create list of transactions
T2 as (
select 1 FileID, T1.ACCT#, CONTR$, 0 DIST$
from T1 join ContTbl on T1.ACCT# = ContTbl.ACCT#
union all
select 2 FileID, T1.ACCT#, 0 CONTR$, DIST$
from T1 join DistTbl on T1.ACCT# = DistTbl.ACCT#)

-- Now list the results
select ACCT#, CONTR$, DIST$ from T2
order by FileID

You may need a little tweaking to make sure the columns match in the two selects for the union in T2, but this is the general
idea. I am a big fan of common table expressions. They work
wonders.

Jack Prucha wrote:

I'm trying to select a list that looks like this:

Acct# Contr$ Dist$
00001 15.00
00001 28.00
00001 30.00
00005 10.00
00005 25.00

etc.

Select accts with at least one contribution and one distribution and list them all. The contribution and
distributions can be on the same line or individual lines,
but there's no matching of them. The selection will be by
date ranges also.

There are three separate tables that look something like
these:

AcctTbl
Acct#

ContTbl
Acct#, Contr$, ContrDate

DistTbl
Acct#, Dist$, DistDate

I can figure out the where exists to select the accounts
but how do I join these together to get the details?