Joining on a SubQuery?
Posted on 2008-10-10
I have following query:
select case when parentid like '%-%' then substring(ParentId, 0, charindex('-',ParentId,1)) else parentid end as Parent from assets )
This pulls back all the ParentID's for my assets.
I then have:
select A.ID, A.AccountID, A.SerialNumber,D.ContactName,A.AssetCategory from Assets as A
left join debitors d on d.accountid = a.parentid
(A.ParentId LIKE '%-l004-%')
OR (A.ParentId LIKE '%l004-%')
OR (A.ParentId LIKE '%-l004%')
OR (A.ParentId LIKE 'l004')
OR (A.AccountId LIKE 'l004')
GROUP BY A.ID, A.AccountID, A.SerialNumber,A.ParentID,A.AssetCategory,d.ContactName,D.AccountID
But due to how I have built up the ParentID, I am left with alot of NULL going into D.ContactName. What I was hoping to do was use the SubQuery to fill in those NULL's but am not sure how I do it.