1

I need to get aggregate data from multiple tables without excluding null values. When I pull the tables into crystal reports, it eliminates the results that have null values.

Example: Table A contains a unique MemberID (which I need to group by) and a field for linking the tables (named ROWNO). Table Demand contains data I need included in a group (named D_BALANCE). Table Loan also contains data I need included in a group (named L_BALANCE), but does not always exist. The tables link using a ROWNO field on table A, rowno_custdmd_cust on table Demand, and rowno_custloan_cust on table Loan.

If the table has been generated for the MemberID then the rowno_custloan_cust will have a value, if the table has not been generated then the value would be NULL.

When I use Crystal Reports to try and pull all MemberID's and get all records of the BALANCE fields from both Table Demand and Table Loan, it eliminates any MemberID (filters out all records) that have a NULL value for rowno_custloan_cust.

I started investigating how to do this in SQL instead of Crystal Reports thinking that I may be able to better control the results, but also stop the results from being removed when they do not contain data in Table Loan.

Here is the code I am inquiring about changing. This is from the SQL import that sends the data to our database.

GO
/****** Object:  Table [dbo].[loan]    Script Date: 15May2022 11:24:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[loan](
[rowno] [int] NOT NULL,
[balance] [money] NOT NULL,
[rowno_custloan_cust] [int] NOT NULL,
 CONSTRAINT [rowno_loan] PRIMARY KEY NONCLUSTERED 
(
    [rowno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

What changes I would need to make or where I should start looking so that I may pull all members from Table A (cust) with any available data from the other two tables (loan and dmd) without it filtering out anyone who does not have a loan form attached? Would it be easiest to just get the data from the balance fields, link them by their key, create a new database with the info and pull from there?

philipxy
  • 14,867
  • 6
  • 39
  • 83
donothingforawhile
  • 19
  • 2
  • 1
    You didn't share any SELECT SQL; only a CREATE TABLE for one of your tables. – tinazmu Jun 23 '22 at 22:56
  • As I see it, the problem could be solved in crystal-reports, using its syntax. Use the clauses like `If Is Null` and put the other value in case it is null. – lsanchezo Jun 23 '22 at 23:55
  • Is this the structure of your 3 tables: Member ( MemberID, MemberRowNo) Demand ( DemandBalance, DemandRowNo MemberRowNo) Loan (LoanBalance, LoanRowNo, MemberRowNo) – Eric Jun 24 '22 at 00:16
  • 1
    You can use a "left outer join" to link tables with null values – Eric Jun 24 '22 at 00:26
  • [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/q/3969452/3404097) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [mre] [ask] [Help] – philipxy Jun 24 '22 at 05:14

1 Answers1

1

Outer Join can take care of the stated problem.

But be aware that if a single Customer record can have multiple matching Demand records as well as multiple Loan records, you would face a problem called "Record Inflation". If that scenario is possible, one of the ways around it is to use 1 View to summarize the Loan totals per customer and 1 View to summarize the Demand totals per customer. Each of these Views would use an Outer Join. You can then join these two views with a regular (Inner) join.

MilletSoftware
  • 3,521
  • 2
  • 12
  • 15