Retrieving Specific Records

[Mark Hargreaves]

Hi,

Hopefully someone can help.

I am trying to establish (using a straight MySQL query in version 4.0.18)
the level of qualifications someone has, i.e. degree, postgraduate degree or
other.

To simplify things, I need to use only two tables, namely:

master table, containing:
staffno (Primary Key);
forename;
surname;

psnquals table, containing:
psnqualid (Primary Key);
staffno (Foreign Key);
levelid (where 1 = degree, 2 = postgraduate degree, 3 = other)

In the followning query, staffno 1 has three qualification levels, 1, 2, 3.
The query finds the first matching record (where cdelevelid = 1) and then
stops, making it appear as if the employee had no postgraduate
qualification.

One person can have one or all of the above qualifications. I need to write
a query that shows if a person has one, two or all of the above. I tried
using the following query, but it seems only to look at the first psnqualid
record it finds and then stops:

SELECT m1.staffno, m1.forename, m1.surname,
CASE WHEN p1.cdelevelid = 1 THEN "D" WHEN p1.cdelevelid = 2 THEN "P" ELSE
"O"
FROM `master` m1
INNER JOIN psnquals p1 ON m1.staffno = p1.staffno

I also tried using an IF statement, but this also seemed to stop after
finding the first matching record in the psnquals table.

Any suggestions wouls be most welcome.

Thanks,

Mark H
---
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

[jerry gitomer]

Mark Hargreaves wrote:

Hi,

Hopefully someone can help.

I am trying to establish (using a straight MySQL query in version 4.0.18)
the level of qualifications someone has, i.e. degree, postgraduate degree or
other.

To simplify things, I need to use only two tables, namely:

master table, containing:
staffno (Primary Key);
forename;
surname;

psnquals table, containing:
psnqualid (Primary Key);
staffno (Foreign Key);
levelid (where 1 = degree, 2 = postgraduate degree, 3 = other)

In the followning query, staffno 1 has three qualification levels, 1, 2, 3.
The query finds the first matching record (where cdelevelid = 1) and then
stops, making it appear as if the employee had no postgraduate
qualification.

One person can have one or all of the above qualifications. I need to write
a query that shows if a person has one, two or all of the above. I tried
using the following query, but it seems only to look at the first psnqualid
record it finds and then stops:

SELECT m1.staffno, m1.forename, m1.surname,
CASE WHEN p1.cdelevelid = 1 THEN "D" WHEN p1.cdelevelid = 2 THEN "P" ELSE
"O"
FROM `master` m1
INNER JOIN psnquals p1 ON m1.staffno = p1.staffno

I also tried using an IF statement, but this also seemed to stop after
finding the first matching record in the psnquals table.

Any suggestions wouls be most welcome.

Thanks,

Mark H
---
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

SELECT staffno, forename, surname, cdelevelid
FROM master

and do the conversion from numeric to alpha levels in the program that
invokes the query.

HTH

[Duane Evenson]

jerry gitomer wrote:

Mark Hargreaves wrote:

Hi,

Hopefully someone can help.

I am trying to establish (using a straight MySQL query in version 4.0.18)
the level of qualifications someone has, i.e. degree, postgraduate
degree or
other.

To simplify things, I need to use only two tables, namely:

master table, containing:
staffno (Primary Key);
forename;
surname;

psnquals table, containing:
psnqualid (Primary Key);
staffno (Foreign Key);
levelid (where 1 = degree, 2 = postgraduate degree, 3 = other)

In the followning query, staffno 1 has three qualification levels, 1,
2, 3.
The query finds the first matching record (where cdelevelid = 1) and
then
stops, making it appear as if the employee had no postgraduate
qualification.

One person can have one or all of the above qualifications. I need to
write
a query that shows if a person has one, two or all of the above. I tried
using the following query, but it seems only to look at the first
psnqualid
record it finds and then stops:

SELECT m1.staffno, m1.forename, m1.surname,
CASE WHEN p1.cdelevelid = 1 THEN "D" WHEN p1.cdelevelid = 2 THEN "P" ELSE
"O"
FROM `master` m1
INNER JOIN psnquals p1 ON m1.staffno = p1.staffno

I also tried using an IF statement, but this also seemed to stop after
finding the first matching record in the psnquals table.

Any suggestions wouls be most welcome.

Thanks,

Mark H
---
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

SELECT staffno, forename, surname, cdelevelid
FROM master

and do the conversion from numeric to alpha levels in the program that
invokes the query.

HTH

Or add a third table level (id INT PRIMARY KEY, code CHAR) with VALUES
(1, "D"), (1, "P", (3, "O"). It's probably better to do lookup with a
table rather than with code.