MySQL Query Help!!

[Mark Hargreaves]

Hopefully someone can help me here.

I have a personnel database with numerous tables, although only a few are
required for this particular query. I will name them as follows:

master - holds personal details about an employee,with the "staffno" field
as primary key;

psnexams - details number and type of exams a person has passed. Linked to
the master table using staffno field.
The fields in this table are:
- staffno - where the master table can be joined;
- psnexamid - primary key;
- cdeexamid - an integer, betwen 1 and 5, representing exam type;
- examresult - where pass = 1, fail = 0

There are 5 types of exams, numbered 1 to 5. These exams are split into 2
categories, namely 1, 2, 3 an 4, 5. In order to achieve an overall pass, an
employee must have passed EITHER the first 3 OR the last 2 (i.e. an employee
should have registered a pass in cdeexamid 1 AND 2 AND 3..... OR 4 AND 5 to
qualify for an overall pass)

I am attempting to write single query that will capture both types of
passes. I have managed to capture a list, but using two queries, shown
below:

SELECT DISTINCT master.payno AS "Pay Number",
master.forename AS "Forename",
master.surname AS "Surname",
count (psnexams.examresult) as "Passes"
from `master`
LEFT JOIN psnexams on master.staffno = psnexams.staffno
where master.inservice = 1
AND psnexams.cdeexamid in (1, 2, 3)
AND psnexams.examresult = 1
group by psnexams.staffno having Passes = 3

OR

SELECT DISTINCT master.payno AS "Pay Number",
master.forename AS "Forename",
master.surname AS "Surname",
count (psnexams.examresult) as "Passes"
from `master`
LEFT JOIN psnexams on master.staffno = psnexams.staffno
where master.inservice = 1
AND psnexams.cdeexamid in (4, 5)
AND psnexams.examresult = 1
group by psnexams.staffno having Passes = 2

What I am looking for is a query that will capture both lists in a single
uery.

Any help would be greatly appreciated.

Sparkybhoy

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.684 / Virus Database: 446 - Release Date: 13/05/2004

[Bill Karwin]

Mark Hargreaves wrote:

What I am looking for is a query that will capture both lists in a single
query.

How about this:

SELECT DISTINCT M.payno AS "Pay Number",
M.forename AS "Forename", M.surname AS "Surname",
COUNT(P1.cdeexamid) + COUNT(P2.cdeexamid) AS Passes
FROM `master` M
INNER JOIN psnexams P1 ON (P1.staffno = M.staffno)
INNER JOIN psnexams P2 ON (P2.staffno = M.staffno)
WHERE M.inservice = 1
AND P1.cdeexamid IN (1,2,3) AND P1.examresult = 1
AND P2.cdeexamid IN (4,5) AND P2.examresult = 1
GROUP BY M.staffno
HAVING COUNT(P1.cdeexamid) = 3 OR COUNT(P2.cdeexamid) = 2;

Bill K.