Need help on a sub query to emulate a LOJ with conditions

1

I've used report writers in the past (Crystal Reports), but am new to doing straight SQL data queries.

I have 3 tables:
Customer (C),
SalesHeader (S),
Sales Line (L).

The table joins are:
[C.Account = S.CustAccount],
[S.OrderID = L.OrderID]

I want to report on 4 columns:
C.Account,
C.CrediLimit,
Expression1 = (Sum of L.Amount Grouped by C.Account).
Expression2 = (C.CreditLimit - Expression1)

Constraints:
Not all Customer records have Sales records. I want to include customers even if no sales records.
I only want to include Sales records where S.OrderType = "3"

My problem:
I was ok with a left outer join between Customer and SalesHeader tables, until I added the selection criteria for "S.OrderType = 3". Then it defaulted to inner join logic and all my customers with no salesheader records disappeared.

My "assumed" solution:
When I've used report writers in the past I would do a sub report, which I assume acts like a sub query.
My idea is that a sub query can function like a LOJ with conditions, so I would do a sub query for Expression1 and Expression2.

My frustrations:
How do I include a multi table join in the sub query statement?
Is the sub query in the SELECT, FROM, WHERE section of the statement?
It was supposed to be JUST a simple little project! :slight_smile:

Any enlightenment out there? I'm feeling pretty dim...

2

Nah, LOJs and/or NULLs can be confusing to everyone at times.

But in this case, all you need to do is specify the extra condition in the LOJ itself (rather than in a WHERE clause):

SELECT ...
FROM dbo.Customer C
LEFT OUTER JOIN dbo.SalesHeader S ON S.CustAccount = C.Account AND S.OrderType = '3'
LEFT OUTER JOIN dbo.SalesLine L ON L.OrderID = S.OrderID
...

3

Hi Scott,
Thanks for the info. I didn’t know you could include selection criteria with a left outer join.
My result was not what I expected, so I might be doing something wrong, or the statement isn’t doing what we expect.
Below are my notes. The table & column names are slightly different than I wrote before, but represent the same data.

  1.  When I do a query of only the Customer table to see their Credit Limit info, I get 4136 records:

SELECT ACCOUNTNUM, NAME, CREDITMAX
FROM CUSTTABLE

  1.  When I do a query linking the Customer, SalesHeader, Sales Line tables  as I think you described, I get 80 records.

What I want to see is all 4136 customer records with their credit limit, and sales table data when it exists.

SELECT MAX(CUSTTABLE.ACCOUNTNUM) AS CUST_ACCT, MAX(CUSTTABLE.NAME) AS CUST_NAME, MAX(CUSTTABLE.CREDITMAX) AS CREDIT_LIMIT,
SUM(SALESLINE.LINEAMOUNT) AS LINE_TOTAL
FROM CUSTTABLE LEFT OUTER JOIN
SALESTABLE ON SALESTABLE.INVOICEACCOUNT = CUSTTABLE.ACCOUNTNUM AND SALESTABLE.DOCUMENTSTATUS = '3' LEFT OUTER JOIN
SALESLINE ON SALESLINE.SALESID = SALESTABLE.SALESID
GROUP BY SALESTABLE.INVOICEACCOUNT

My alternate logic is to not include the SALESTABLE.DOCUMENTSTATUS = '3' in the selection, but to do a calculated column. See the example below (I added some new elements).

SELECT MAX(CUSTTABLE.ACCOUNTNUM) AS CUST_ACCT, MAX(CUSTTABLE.NAME) AS CUST_NAME, MAX(CUSTTABLE.CUSTGROUP) AS CUSTGROUP,
MAX(CUSTTABLE.GG_CREDITLIMITREVIEW) AS CREDIT_LIMIT_REVIEW, MAX(CUSTTABLE.CREDITMAX) AS CREDIT_LIMIT,
SUM(CASE WHEN SALESTABLE.DOCUMENTSTATUS = '3' THEN SALESLINE.LINEAMOUNT ELSE 0 END) AS CONFIRMED_ORD_TOTAL, MAX(CUSTTABLE.CREDITMAX)
- SUM(CASE WHEN SALESTABLE.DOCUMENTSTATUS = '3' THEN SALESLINE.LINEAMOUNT ELSE 0 END) AS CREDIT_AVAILABLE
FROM CUSTTABLE LEFT OUTER JOIN
SALESTABLE ON SALESTABLE.INVOICEACCOUNT = CUSTTABLE.ACCOUNTNUM LEFT OUTER JOIN
SALESLINE ON SALESLINE.SALESID = SALESTABLE.SALESID
GROUP BY SALESTABLE.INVOICEACCOUNT

This query returned 1141 records, which is more than 80. My suspicion is that the outer join is still only including those customer table records where a sales table record exists.
Functionally, this will probably work, since the only customers with no sales table records are ones who have never placed an order.
But I’d like to understand how to get all the customer records.

Any insight out there?

Clark

Clark Walliser | Business System Analyst
Gigamon®| 3300 Olcott Street, Santa Clara, CA 95054
Office: 408 831.4375
www.gigamon.comhttp://www.gigamon.com

4

If you want to see all the detail rows, you'll need to move the groupings into derived tables:

SELECT CT.ACCOUNTNUM AS CUST_ACCT, 
    CT.NAME AS CUST_NAME, 
    CT_MAX.CREDIT_LIMIT,
    ST_TOTALS.LINE_TOTAL
FROM CUSTTABLE CT
INNER JOIN (
    SELECT ACCOUNTNUM, MAX(CUSTTABLE.CREDITMAX) AS CREDIT_LIMIT
    FROM CUSTTABLE
    GROUP BY ACCOUNTNUM
) AS CT_MAX ON CT_MAX.ACCOUNTNUM = CT.ACCOUNTNUM
LEFT OUTER JOIN (
    SELECT ST.INVOICEACCOUNT, SUM(SL.LINEAMOUNT) AS LINE_TOTAL
    FROM SALESTABLE ST
    LEFT OUTER JOIN SALESLINE SL ON SL.SALESID = ST.SALESID
    WHERE ST.DOCUMENTSTATUS = '3'
    GROUP BY ST.INVOICEACCOUNT
) AS ST_TOTALS ON ST_Totals.INVOICEACCOUNT = CUSTTABLE.ACCOUNTNUM
ORDER BY CT.ACCOUNTNUM, ST_TOTALS.LINE_TOTAL DESC