| Author |
 Topic  |
|
|
no_mike
Starting Member
USA
5 Posts |
 Posted - 09/27/2012 : 13:28:59
|
This query returns what utilities are to be listed depending on a resident and works fine:
SELECT S.SDESC AS 'Service'
FROM UTALLOW A
LEFT JOIN UTALLOWDET AD ON A.HMY = AD.HUTALLOW
LEFT JOIN UTILSERV S ON AD.HUTILSERV = S.HMY
LEFT JOIN H8UNIT HU ON A.HMY = HU.HUTSCHEDULE
LEFT JOIN UNIT U ON HU.HUNIT = U.HMY
LEFT JOIN H8SUMM HS ON U.HMY = HS.HUNIT
WHERE HS.HTENANT = @resident_id
AND S.HMY IN
(SELECT HUTILSERV
FROM UTRESP R
LEFT JOIN H8UNIT HU ON R.HH8UNIT = HU.HMY
LEFT JOIN UNIT U ON HU.HUNIT = U.HMY
LEFT JOIN H8SUMM HS ON U.HMY = HS.HUNIT
WHERE HS.HTENANT = @resident_id
GROUP BY HUTILSERV)
GROUP BY S.SDESC
ORDER BY S.SDESC
Results:
Service
--------------------------------------------------------------------------------
Air Conditioning
Cooking-Electric
Heating-Electric
Monthly Electric Fee allowance
Other Electric
Range
etc....
However, now I need to add a column indicating whether or not the owner is responsible for specific utilities. The BOWNER columns will always be either 0 or -1 (never NULL). When I add the JOIN to the UTRESP table it returns each service twice as below. What is the proper way to go about this? I'm still working on it but any guidance would be appreciated.
SELECT S.SDESC AS 'Service',
CASE WHEN R.BOWNER = 0 THEN 'No' ELSE 'Yes' END AS 'OWNER_RESP'
FROM UTALLOW A
LEFT JOIN UTALLOWDET AD ON A.HMY = AD.HUTALLOW
LEFT JOIN UTILSERV S ON AD.HUTILSERV = S.HMY
LEFT JOIN UTRESP R ON S.HMY = R.HUTILSERV
LEFT JOIN H8UNIT HU ON A.HMY = HU.HUTSCHEDULE
LEFT JOIN UNIT U ON HU.HUNIT = U.HMY
LEFT JOIN H8SUMM HS ON U.HMY = HS.HUNIT
WHERE HS.HTENANT = @resident_id
AND S.HMY IN
(SELECT HUTILSERV
FROM UTRESP R
LEFT JOIN H8UNIT HU ON R.HH8UNIT = HU.HMY
LEFT JOIN UNIT U ON HU.HUNIT = U.HMY
LEFT JOIN H8SUMM HS ON U.HMY = HS.HUNIT
WHERE HS.HTENANT = @resident_id
GROUP BY HUTILSERV)
GROUP BY S.SDESC,R.BOWNER
ORDER BY S.SDESC
Results:
Service OWNER_RESP
-------------------------------------------------------------------------------- ----------
Air Conditioning Yes
Air Conditioning No
Cooking-Electric Yes
Cooking-Electric No
Heating-Electric Yes
Heating-Electric No
etc...
Also, if anybody sees a way that it would be possible to remove the subquery I'm all ears. Just getting started on this one from a DB that's been around for a while and I'm new to.
Thanks,
Mike |
Edited by - no_mike on 09/27/2012 16:33:21
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
no_mike
Starting Member
USA
5 Posts |
Posted - 09/27/2012 : 16:35:07
|
| Thanks, Tara. I edited my post to reflect your observation. Any suggestions on how to remedy my issue? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47155 Posts |
Posted - 09/27/2012 : 16:49:24
|
quote:
Originally posted by no_mike
Thanks, Tara. I edited my post to reflect your observation. Any suggestions on how to remedy my issue?
so whats output that you're expecting?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
no_mike
Starting Member
USA
5 Posts |
Posted - 09/27/2012 : 16:57:31
|
Service OWNER_RESP
-------------------------------------------------------------------------------- ----------
Air Conditioning Yes
Cooking-Electric Yes
Heating-Electric No
etc...
Basically, it should be either yes or no for the OWNER_RESP field as only one value is given (either 0 or -1) |
Edited by - no_mike on 09/27/2012 16:59:47 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47155 Posts |
Posted - 09/27/2012 : 16:59:10
|
so whats basis for choosing a value? how do you determine whether its yes or a no to be linked to a service? do you've a mapping table which contains information on shich services a owner has responsibility on?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
no_mike
Starting Member
USA
5 Posts |
Posted - 09/27/2012 : 17:11:08
|
Yes...In the H8SUMM table there is a TENANT. The tenant row has a column linking to the UNIT table. Then the UNIT table has a FK to the H8UNIT table. In the H8UNIT table there is a field HUTSCHEDULE which determines the utility "schedule" that a this unit is on. Depending on the schedule, specific types of utilities are available to possibly be paid for by the landlord. The UTRESP table is the key. Each unit can be listed multiple times with a FK to the utility/service that is going to be paid by either the landlord or tenant. For example
Unit __ Service
581 __ 2
581 __ 4
581 __ 5
581 __ 9
etc....
Sorry, this is really hard to explain and I fear that this may sound like a bunch of jumbled up words. Let me know if this makes no sense and to forget about it, you would like more description, or if you have any ideas.
SELECT S.SDESC AS 'Service',
CASE WHEN R.BOWNER = 0 THEN 'No' ELSE 'Yes' END AS 'OWNER_RESP'
FROM UTALLOW A
LEFT JOIN UTALLOWDET AD ON A.HMY = AD.HUTALLOW
LEFT JOIN UTILSERV S ON AD.HUTILSERV = S.HMY
LEFT JOIN UTRESP R ON S.HMY = R.HUTILSERV
LEFT JOIN H8UNIT HU ON A.HMY = HU.HUTSCHEDULE
LEFT JOIN UNIT U ON HU.HUNIT = U.HMY
LEFT JOIN H8SUMM HS ON U.HMY = HS.HUNIT
WHERE HS.HTENANT = @resident_id
AND S.HMY IN
(SELECT HUTILSERV
FROM UTRESP R
LEFT JOIN H8UNIT HU ON R.HH8UNIT = HU.HMY
LEFT JOIN UNIT U ON HU.HUNIT = U.HMY
LEFT JOIN H8SUMM HS ON U.HMY = HS.HUNIT
WHERE HS.HTENANT = @resident_id
GROUP BY HUTILSERV)
GROUP BY S.SDESC,R.BOWNER
ORDER BY S.SDESC
|
Edited by - no_mike on 09/27/2012 17:12:17 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47155 Posts |
|
|
no_mike
Starting Member
USA
5 Posts |
Posted - 09/27/2012 : 17:38:50
|
| Thanks for taking a look at it. I run into problems like these somewhat often working with these old 3rd party DB's that I have no point of reference with....I'm going to try rewriting it from scratch a couple of times and then if I still can't figure it out I will give your posting recommendation a shot. Thanks a lot though! |
|
|
| |
Topic |
|
Help with CASE Statement Using JOINS
