mgmhicks
asked on
help with sql syntax
I have the following sql statement that doesn't quite work for me. I want to list the records from table a join a couple of more, for the records that do not include the same propertyid,bldgid,unitid,r esiid from the collections table. I do not want to include the resident in the list if there is already a record in table2 with the same propertyid,bldgid,unitid,r esiid.
thanks in advance
Select a.propertyid+a.bldgid+a.unitid+cast(a.resiid as char(5))as residentID,a.propertyid,a.bldgid,a.unitid,a.resiid,a.transamt,a.GLPeriod,b.ResiFirstName,b.ResiLastName,b.ResiStatus,b.moveindate,c.MoveOutDate,c.MoveOutCode from tag_esite.dbo.TransactionHeader a
right outer join lease b on a.PropertyId=b.PropertyId and a.BldgId=b.BldgId and a.UnitId=b.UnitId and a.ResiId=b.Resiid
right outer join leasemoveout c on b.PropertyId=c.PropertyId and b.BldgId=c.BldgId and b.UnitId=c.UnitId and b.ResiId=c.ResiId
where a.sourcecode='wo' and a.propertyid='cl' and a.propertyid+a.bldgid+a.unitid+cast(a.resiid as CHAR(5)) not in (select propertyid+bldgid+unitid+cast(resiid as char(5)) as residentid from aptgallery.dbo.Collections_Master)
order by a.PropertyId,a.BldgId,a.UnitId,a.resiid
thanks in advance
My interpretation, show me records from TransactionHeader, except for residents in:
1. Collections_Master
2. lease
2. lease
select a.propertyid+a.bldgid+a.unitid+cast(a.resiid as char(5)) as residentID
, a.propertyid
, a.bldgid
, a.unitid
, a.resiid
, a.transamt
, a.GLPeriod
, b.ResiFirstName
, b.ResiLastName
, b.ResiStatus
, b.moveindate
, c.MoveOutDate
, c.MoveOutCode
from tag_esite.dbo.TransactionHeader a
left
join lease b on a.PropertyId = b.PropertyId
and a.BldgId = b.BldgId
and a.UnitId = b.UnitId
and a.ResiId = b.ResiId
left
join leasemoveout c on a.PropertyId = c.PropertyId
and a.BldgId = c.BldgId
and a.UnitId = c.UnitId
and a.ResiId = c.ResiId
left
join aptgallery.dbo.Collections_Master d on a.PropertyId = d.PropertyId
and a.BldgId = d.BldgId
and a.UnitId = d.UnitId
and a.ResiId = d.ResiId
where a.sourcecode = 'wo'
and a.propertyid = 'cl'
and b.ResiId IS NULL
and d.ResiId IS NULL
order
by a.PropertyId
, a.BldgId
, a.UnitId
, a.resiid
ASKER
what isn't working is that I get all records from transaction Header with a WO sourcecode, but I only want all records from transactionheader with a WO sourcecode, that doesn't exist in the collections_master table. What makes the records exist is the propertyid,bldgid, unitid, and resiid all being the same.
thanks
thanks
Why did you choose to use RIGHT OUTER JOINs for lease and leasemoveout?
ASKER
I only want a single record from the transactionheader and then a join so that I can keep just a single record. See Ideally is I get the sum of the 'WO' sourcecode amount for each individual propertyid,bldgid,unitid, and resiid. So know I have the property,bldgid,unitid and resiid as a single record and the 'WO' transaction sum as a column in that single record. Then I need to join the lease table to receive a little data and the leasemoveout for yet a little more data, all pertaining to the unique property,bldgid,unitid,res iid.
So a resident may have multiple 'WO' transactions I need the sum for each individual record, and then joining by property,bldging,unitid,re siid to other tables get the other bit of information like moveout date and stuff.
Hope that clears it up more rather than making it worse.
So a resident may have multiple 'WO' transactions I need the sum for each individual record, and then joining by property,bldging,unitid,re
Hope that clears it up more rather than making it worse.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
Select a.propertyid+a.bldgid+a.un
a.propertyid,a.bldgid,a.un
b.ResiFirstName,b.ResiLast
c.MoveOutDate,c.MoveOutCod
from tag_esite.dbo.TransactionH
right outer join lease b
on a.PropertyId=b.PropertyId
and a.BldgId=b.BldgId
and a.UnitId=b.UnitId
and a.ResiId=b.Resiid
right outer join leasemoveout c
on b.PropertyId=c.PropertyId
and b.BldgId=c.BldgId
and b.UnitId=c.UnitId
and b.ResiId=c.ResiId
LEFT OUTER JOIN aptgallery.dbo.Collections
ON a.PropertyId=collections.P
and a.BldgId=collections.BldgI
and a.UnitId=collections.UnitI
and a.ResiId=collections.Resii
where a.sourcecode='wo'
and a.propertyid='cl'
and collections.PropertyId IS NULL
order by a.PropertyId,a.BldgId,a.Un