asked on

SQL Query...

I have a candidates table where candidates can choose upto 5 sectors, sector1, sector2 etc. These values come from a lookup table industry_sectors.
How do I find the total number of candidates that have chosen each industry_sector? I would like this as a single query and would also like to join in the sectorname...

Candidates:
Sector1
Sector2
Sector3
Sector4
Sector5

Industry_Sectors:
ID
SectorName

DrTech

You have made it almost impossible to make such a query, by designing a poor data model.

Candidates and sector have a Many-To-Many relationship, thus you should have a third table (i call it CandidateSector) that links the candidate to the sections, containing the fields CandidateID and SectorID

Then you would have a record for each sector, that the candidate has chosen, and the query you want would be quite simple.

select b.sectorname, count(a.CandidateId)
from CandidateSector a, Sector b
where a.sectorid=b.sectorid
group by b.sectorname

Lowfatspread

I agree with DrTech, you table design needs to be looked at
in regard to what you are trying to achieve...

you can doit with 2 queries

the query you execute
SELECT Sect,Count(*)
FROM sect INNER JOIN sects ON sect.sectid = sects.sect
group by sect;

Sect
SELECT cand.candid, cand.sect1 as sect
FROM cand
union
SELECT cand.candid, cand.sect2 as sect
FROM cand
union
SELECT cand.candid, cand.sect3 as sect
FROM cand
union
SELECT cand.candid, cand.sect4 as sect
FROM cand
UNION SELECT cand.candid, cand.sect5 as sect
FROM cand;

I assume its an Access Query...

ASKER CERTIFIED SOLUTION

Binary1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

juststephen

ASKER

You know - I think that's done the trick: let me do a bit of checking and the points may soon be yours binary1 !

juststephen

ASKER

You know - I think that's done the trick: let me do a bit of checking and the points may soon be yours binary1 !

DrTech

Just remember (as this solution proves):

Messy datamodel = Messy code