problem i outer select
Dette er i forlængelse af spm.: http://www.eksperten.dk/spm/657935SELECT wr.buildingnum, wr.residencenum, count(*) as tal
FROM wait_residence wr (nolock)
inner join wait_company wc (nolock) on (wr.fk_companyid = wc.id and wc.name = 'Sekundær')
inner join wait_applicantapplication waa (nolock) on (wr.id = waa.fk_residence)
GROUP BY wr.residencenum, wr.buildingnum, wr.companynum
HAVING count(waa.id) > 0
dette giver nok så fint resultatet
157 3004 1
157 4002 2
hvilket er helt ok.
Men sætter jeg den stump kode ind i mit script bliver resultatet anderledes - se her.
SELECT A1.companynum, A1.buildingnum, A1.residencenum,/* B1.buildingnum, B1.residencenum,*/ B1.tal
FROM ( SELECT wr.companynum, wr.buildingnum, wr.residencenum
FROM
wait_residence wr
inner join wait_company wc (nolock) on (wr.fk_companyid = wc.id and wc.name = 'Sekundær')
inner join wait_residence_freestatus wrfs (nolock) on (wr.id = wrfs.fk_residenceid)
inner join wait_building wb (nolock) on (wb.id = wr.fk_buildingid)
left join wait_residence_offerstatus wros (nolock) on (wr.id = wros.fk_residenceid)
inner join wait_applicantapplication waa (nolock) on (wr.id = waa.fk_residence)
inner join wait_applicant wa (nolock) on (waa.fk_applicant = wa.id)
--inner join wait_applicantparameterdata wapd (nolock) on (wapd.fk_systemparamdef = '94EA3254-6C2D-406b-A027-ABCA74C652D8' and wa.id = wapd.fk_applicant)
/*inner join wait_applicantcompany wac (nolock)
on (wa.id = wac.fk_applicant and wac.fk_company = wc.id)*/
WHERE
wr.deletedate is null
and wrfs.occupieddate is null
and wros.deletedate is null
--and wros.offered = 0 -- ?? ved ikke om denne skal med
and wb.deletedate is null
--and wr.nonsearchable is null
--and wac.verified = 0 -- at ansøgningen er verified
and wa.status = 1 -- aktive ansøgere
and waa.createdate > DATEADD(dd, -2, GETDATE())
) AS A1
JOIN ( SELECT wr.buildingnum, wr.residencenum, count(*) as tal
FROM wait_residence wr (nolock)
inner join wait_company wc (nolock) on (wr.fk_companyid = wc.id and wc.name = 'Sekundær')
inner join wait_applicantapplication waa (nolock) on (wr.id = waa.fk_residence)
GROUP BY wr.residencenum, wr.buildingnum, wr.companynum
HAVING count(waa.id) > 0
--ORDER BY wr.companynum, wr.buildingnum, wr.residencenum
) AS B1 ON ( a1.buildingnum = b1.buildingnum and a1.residencenum = b1.residencenum)
giver nu dette resultat:
11 157 3004 2
11 157 4002 2
Jeg forstår ikke hvorfor at der pludselig står 2 i stedet for 1 i først række.