Joining Multiple matches from Right Table to 1 row

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

SQL Server 2008 Forums

Transact-SQL (2008)

Joining Multiple matches from Right Table to 1 row

Author

Topic

spsubaseball
Starting Member

17 Posts

Posted - 2012-11-28 : 07:34:04

I have a cross reference table, attributexref, which holds values of different fields based distinguished by hattributename. I am wanting to populate a single row for each property record, and have columns for each of the different attribute types from attributexref. Instead it is populating a separate row for each match in the attributexref table. Any ideas?

Here is the code I currently have:


Select distinct
p.scode, 
unit.scode,
case av.hattributename when 7 then av.sValue Else '' end,
p.sAddr2
, p.sCity
, p.sState
, p.sZipCode
, case av.hattributename when 8 then av.sValue Else '' end
, case av.hattributename when 34 then av.sValue Else '' end
, PROPBUT8.DUPSTATUS
, case av.hattributename when 5 then av.sValue Else '' end
, PROPBUT13.ACQUISITIONDATE
, PROPBUT13.ACQUISITIONPRICE
, PROPBUT13.ALLINACTUAL
, PROPBUT13.ALLINSTABLE
, PROPBUT13.STABLEDT
, tenant.sRent
, Case tenant.iStatus When 0 Then 'Current'  When 1 Then 'Past'  When 2 Then 'Future'  When 3 Then 'Eviction'  When 4 Then 'Notice'  When 5 Then 'Vacant'  When 6 Then 'Applicant'  When 7 Then 'Cancelled'  When 8 Then 'Wait list'  When 9 Then 'Denied'  Else ' '  End
, PROPBUT8.CITYTAX
, PROPBUT8.COUNTYTAX
, PROPBUT8.REFINANCEDSTATUS
, PROPBUT8.DATEREFINANCED
, PROPBUT8.FINANCELENDER
From  Property p  
Inner Join unit ON p.hMy = unit.hProperty
Left Outer Join PROPBUT13 ON p.hMy = PROPBUT13.hCode  
Left Outer Join PROPBUT8 ON p.hMy = PROPBUT8.hCode  
Inner Join Tenant ON p.hMy = tenant.hProperty
inner join attributexref attr on p.hmy = attr.hFileRecord and attr.iFileType = 3
inner join attributevalue av on attr.hattributevalue = av.hmy
Where  1= 1

Here are the results I'm currently getting:

00051201 00051201 5 Rolling Hill Ct Columbia SC 29172 NULL NULL NULL NULL NULL NULL 650.00 Current NULL NULL NULL NULL NULL
00051201 00051201 5 Rolling Hill Ct Columbia SC 29172 NULL Bulk Buy NULL NULL NULL NULL NULL 650.00 Current NULL NULL NULL NULL NULL
00051201 00051201 5 Rolling Hill Ct Columbia SC 29172 Oct NULL NULL NULL NULL NULL NULL 650.00 Current NULL NULL NULL NULL NULL
00051201 00051201 BLTREJV3 Columbia LLC 5 Rolling Hill Ct Columbia SC 29172 NULL NULL NULL NULL NULL NULL 650.00 Current NULL NULL NULL NULL NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources