Display results even when there are none.

Question

Post reply

Display results even when there are none.

iklektic

Ten Centuries

Points: 1236
More actions
May 13, 2010 at 4:11 pm

#221479

Hi everyone, I'm trying to build a report that will list sales information from a defined date range.
The problem I'm running into is when there are no results for one of the staff, I would like to be able to still list that persons information with the zero results beneath it. the Table that the staff is on is separate from the other data and I've tried the LEFT OUTER JOIN and the FULL OUTER JOIN to incorporate all the reps, but it is still not coming up.
SELECT *
FROM AllWorkOrderItemReportView INNER JOIN
LineItemServiceReportView ON
AllWorkOrderItemReportView.LineItemServiceReportView_LineItemServiceID = LineItemServiceReportView.LineItemServiceID INNER JOIN
Affiliate ON AllWorkOrderItemReportView.AffiliateID = Affiliate.AffiliateID INNER JOIN
Customer ON Affiliate.CustomerID = Customer.CustomerID INNER JOIN
InvoiceFastFindView ON AllWorkOrderItemReportView.AllWorkOrderComponentView_InvoiceID = InvoiceFastFindView.InvoiceID FULL OUTER JOIN
Staff ON Customer.SalesRepStaffID = Staff.StaffID
WHERE (AllWorkOrderItemReportView.AllWorkOrderComponentView_InvoiceAdjustment IS NOT NULL) AND (dbo.DateOnly(AllWorkOrderItemReportView.AllWorkOrderComponentView_InvoiceAdjustedOn) BETWEEN dbo.DateOnly(@StartDate) AND
dbo.DateOnly(@EndDate))

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The way you've arranged your tables you probably need a RIGHT OUTER JOIN to get all values from Staff.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

You want an outer join, but you will likely need to simplify your query and build it step by step.

First, don't use select *. Pick up specific fields, as an example, here's a basis query that should help you:

SELECT

c.CustomerID,

s.StaffID,

awo.LineItemServiceReportView_LineItemServiceID,

a.affiliateID

FROM AllWorkOrderItemReportView awo

INNER JOIN LineItemServiceReportView lisrv

ON awo.LineItemServiceReportView_LineItemServiceID = lisrv.LineItemServiceID

INNER JOIN Affiliate a

ON AWO.AffiliateID = A.AffiliateID

INNER JOIN Customer c

ON A.CustomerID = C.CustomerID

RIGHT OUTER JOIN Staff s

ON C.SalesRepStaffID = S.StaffID

WHERE (AllWorkOrderItemReportView.AllWorkOrderComponentView_InvoiceAdjustment IS NOT NULL)

AND (dbo.DateOnly(AllWorkOrderItemReportView.AllWorkOrderComponentView_InvoiceAdjustedOn) BETWEEN dbo.DateOnly(@StartDate)

AND dbo.DateOnly(@EndDate))

iklektic Ten Centuries Points: 1236 More actions · Answer 3

Thank you for your response, I had tried the right outer join as well, but that didn't return the desired results.

iklektic Ten Centuries Points: 1236 More actions · Answer 4

Thank you Mr Jones, for your response, I used the asterisk to help focus on the FROM information. I can see how that was misleading, the data I have in the SELECT table is limited to only that which I needed, if you'd like me to I can send it to you?