Help with CASE Statement Using JOINS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with CASE Statement Using JOINS

Author

Topic

no_mike
Starting Member

5 Posts

Posted - 2012-09-27 : 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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-27 : 13:55:17

You have "duplicates" because you added a join to UTRESP which contains the "duplicate" data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

no_mike
Starting Member

5 Posts

Posted - 2012-09-27 : 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

52326 Posts

Posted - 2012-09-27 : 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

5 Posts

Posted - 2012-09-27 : 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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 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

5 Posts

Posted - 2012-09-27 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 17:33:33

sorry still doesnt give me a clear picture

can you try giving data in below format ?
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

no_mike
Starting Member

5 Posts

Posted - 2012-09-27 : 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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources