Retrieving Specific Records

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

re: Retrieving Specific Records

Mark Hargreaves wrote:[color=blue]
> 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
>
>[/color]
SELECT staffno, forename, surname, cdelevelid
FROM master

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

HTH

re: Retrieving Specific Records

jerry gitomer wrote:[color=blue]
> Mark Hargreaves wrote:
>[color=green]
>> 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
>>
>>[/color]
> SELECT staffno, forename, surname, cdelevelid
> FROM master
>
> and do the conversion from numeric to alpha levels in the program that
> invokes the query.
>
> HTH[/color]
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.