January 26, 2009 at 5:00 pm
I have three tables as below. How can I query them to provide the result below?
PersonName
1John
2Jason
3Chris
4David
PersonTotalDistrict
1$50 101
2$75 102
3$90 103
DistrictDistrictName
101North
102South
103Mid-Atlantic
104West
RESULT:
PersonNameDistrictDistrictNameTotal
1John101North $50
2Jason102South $75
3Chris103Mid-Atlantic$90
4David104West $0
January 26, 2009 at 5:06 pm
Before anyone jumps in and does your homework, why don't you show us what you have done so far to solver your problem. This is really an easy thing to accomplish and it won't help you much if we just give you the answer without at least having you give it a go first.
Something to do with giving a fish and learning to fish...
January 26, 2009 at 5:10 pm
kabolick (1/26/2009)
I have three tables as below. How can I query them to provide the result below?PersonName
1John
2Jason
3Chris
4David
PersonTotalDistrict
1$50 101
2$75 102
3$90 103
DistrictDistrictName
101North
102South
103Mid-Atlantic
104West
RESULT:
PersonNameDistrictDistrictNameTotal
1John101North $50
2Jason102South $75
3Chris103Mid-Atlantic$90
4David104West $0
Also, looking at the requested RESULT, not going to happen as there is NO way to link David to District 104 (West).
January 26, 2009 at 5:38 pm
Ok, you're right about the result. I think this is an over simplified example of a problem I am having for a query for an SSRS report, not my homework 🙂 Basically, I have a table that stores metrics. The SSRS report has a parameter value on the Anchor_Event that retrieves only those records for that Anchor_Event, obviously. However, I need the query to pull all Metrics regardless of the Anchor_Event having a value for that metric.
Below is the result of the basic query
SELECT QRY_AR_DASHBOARD_REPORTING_SUMMARY_STATUS_METRIC.TransitionName,
QRY_AR_DASHBOARD_REPORTING_SUMMARY_STATUS_METRIC.MetricDescription,
QRY_AR_DASHBOARD_REPORTING_SUMMARY_STATUS_METRIC.Status, QRY_AR_DASHBOARD_REPORTING_SUMMARY_STATUS_METRIC.Phase,
QRY_AR_DASHBOARD_REPORTING_SUMMARY_STATUS_METRIC.Anchor_Event, TBL_METRIC.MetricID, TBL_ANCHOR_EVENT.AnchorEventId
FROM QRY_AR_DASHBOARD_REPORTING_SUMMARY_STATUS_METRIC INNER JOIN
TBL_ANCHOR_EVENT ON
QRY_AR_DASHBOARD_REPORTING_SUMMARY_STATUS_METRIC.Anchor_Event = TBL_ANCHOR_EVENT.AnchorEvent INNER JOIN
TBL_METRIC ON QRY_AR_DASHBOARD_REPORTING_SUMMARY_STATUS_METRIC.MetricDescription = TBL_METRIC.MetricDescription
TransitionNameMetricDescriptionStatusAnchor_EventMetricIdAnchorEventId
TEST1DESC12Phase1A121
TEST1DESC21Phase1A141
TEST1DESC31Phae1A151
TEST1DESC41Phase2A171
TEST1DESC53Phase1A1111
If I perform an Outer Join on the Metrics table I get the following:
TEST1DESC12Phase1AD1 21
NULLNULLNULLNULLNULL3NULL
TEST2DESC21Phase1AD1 41
TEST3DESC31Phase1AD1 51
NULLNULLNULLNULLNULL6NULL
TEST4DESC41Phase2AD1 71
NULLNULLNULLNULLNULL8NULL
NULLNULLNULLNULLNULL9NULL
NULLNULLNULLNULLNULL10NULL
TEST5DESC53Phase1AD1 111
So, when the parameter value is selected, the nulls will never appear on the report. I need to show all Metrics (Columns) for all Anchor_Events (Rows) in my matrix table in my report.
January 26, 2009 at 7:09 pm
LEFT JOIN TBL_ANCHOR_EVENT
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply