I'm working on this query in a stored procedure. The data structure in simplified terms looks like this: a table containing the tables:
- Person
- Elec_Nominations
- Elec_Vacancy
- Ward
- Sector
What happens is a number of vacant seats are set for a sector within a ward. Individuals that are nominated for a seat are inputted into the Person database and linked to Elec_Nominations. This nomination is linked to Ward through Elec_Vacancy. The Nomination is also linked to the Sector (ELEC_SPEAKER_SECTOR) through Elec_Vacancy and some further link tables:
The query I'm working on
SELECT DISTINCT
p.PERSON_ID,
p.ID_NUMBER,
CONCAT(p.FULLNAMES, (CONCAT((' ' COLLATE Latin1_General_CI_AS), p.SURNAME))) AS NAME,
o.ORG_NAME,
eiw.INTEREST_WARD_ID,
scw.SUBCOUNCIL_ID,
w.WARD_ID,
ess.SPEAKER_SECTOR_ID,
(SELECT TOP 1
essg.NO_OF_SEATS
FROM
ELEC_SPEAKER_SECTOR_GROUP essg
INNER JOIN ELEC_VACANCY ev ON essg.VACANCY_ID = ev.VACANCY_ID
WHERE
ev.VACANCY_ID = en.VACANCY_ID
ORDER BY
essg.ELEC_SECTOR_GROUP_SECTORS_ID DESC) AS SECTORSEATS,
en.SECTOR_TEXT AS SECTORTEXT
--essg.NO_OF_SEATS AS [SECTORSEATS]
FROM
PERSON p
RIGHT OUTER JOIN
ELEC_NOMINATIONS en ON p.PERSON_ID = en.PERSON_ID
RIGHT OUTER JOIN
ELEC_INTEREST_WARD eiw ON en.INTEREST_WARD_ID = eiw.INTEREST_WARD_ID
LEFT JOIN
ELEC_INTEREST_GROUP eig ON eiw.INTEREST_GROUP_ID = eig.INTEREST_GROUP_ID
LEFT JOIN
ORGANISATION o ON eig.ORG_ID = o.ORG_ID
LEFT JOIN
WARD w ON eiw.WARD_ID = w.WARD_ID
LEFT JOIN
ELEC_SPEAKER_SECTOR ess ON eiw.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID
LEFT JOIN
SUBCOUNCILWARD scw ON w.WARD_ID = scw.WARD_ID
--INNER JOIN ELEC_VACANCY ev ON en.VACANCY_ID = ev.VACANCY_ID
--INNER JOIN ELEC_SPEAKER_SECTOR_GROUP essg ON ev.VACANCY_ID = essg.VACANCY_ID
WHERE
ess.SPEAKER_SECTOR_ID IN (SELECT s.item FROM ufn_SplitIntArray(@P_Sectors, ',') s)
AND w.WARD_ID IN (SELECT s.item FROM ufn_SplitIntArray(@P_Wards, ',') s)
AND o.ORG_ID = @P_Org_ID
--AND en.ELECTED = 1
ORDER BY
w.WARD_ID
The output of this query go's into this GridView:
OK, I still need to get some clarity from the client as to how the sector seats are determined. The problem is that if I say en.Elected = 1, it shows only the persons who were elected as it should, but it doesn't show the sectors and wards where there isn't even a vacancy (ie. the 3rd and 5th row won't show).
As I'm typing this, I thought of something. I will try and research to determine if I can do something like this:
AND EVAL(ISNULL(en.NOMINATION_ID, 1))
Or something in SQL like:
if(en.NOMINATION_ID == null)
true
else
en.Elected == true
