SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with CASE Statement Using JOINS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

no_mike
Starting Member

USA
5 Posts

Posted - 09/27/2012 :  13:28:59  Show Profile  Reply with Quote
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
36990 Posts

Posted - 09/27/2012 :  13:55:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

no_mike
Starting Member

USA
5 Posts

Posted - 09/27/2012 :  16:35:07  Show Profile  Reply with Quote
Thanks, Tara. I edited my post to reflect your observation. Any suggestions on how to remedy my issue?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  16:49:24  Show Profile  Reply with Quote
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/

Go to Top of Page

no_mike
Starting Member

USA
5 Posts

Posted - 09/27/2012 :  16:57:31  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  16:59:10  Show Profile  Reply with Quote
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/

Go to Top of Page

no_mike
Starting Member

USA
5 Posts

Posted - 09/27/2012 :  17:11:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  17:33:33  Show Profile  Reply with Quote
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/

Go to Top of Page

no_mike
Starting Member

USA
5 Posts

Posted - 09/27/2012 :  17:38:50  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000