Selecting specific records from one to many table relationship

[Mark Hargreaves]

Hopefully someone can help.

I have two tables, namely master and postings:

Fields include the following:

master:
staffno - int (11) primary key
forename - varchar (20)
surname - varchar (30)

postings:
postingid - int (11) primary key
staffno - int (11) foreign key
duties - text
datestarted - date
dateleft - date

One staff member can have many postings. What I require is to find ONLY the
last posting each person was in. This is the posting with the highest
postingid, or the most recent dateleft.

I tried the following query, which only returned the first posting found in
the join:

SELECT master.staffno,
master.forename ,
master.surname,
max(posting.pos tingid),
posting.duties
from master
left join postings on master.staffno = postings.staffn o
where postings.postin gid is not null

This will return the maximum postingid , which I want, but the duties field
refers to the text found in the first match. Ideally I would like the query
to look at the posting table in reverse order when it is completing the
join.

For the record, I am using MySQL 4.0.17, so subqueries are not an option.

Any help would be appreciated.

Regards,

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:

SELECT master.staffno,
master.forename ,
master.surname,
max(posting.pos tingid),
posting.duties
from master
left join postings on master.staffno = postings.staffn o
where postings.postin gid is not null

This will return the maximum postingid , which I want, but the duties field
refers to the text found in the first match. Ideally I would like the query
to look at the posting table in reverse order when it is completing the
join.

For the record, I am using MySQL 4.0.17, so subqueries are not an option.

Without subqueries, I believe you can't do this in one query.

Here's pseudocode:

@postingid_list = SELECT MAX(P.postingid )
FROM postings P GROUP BY P.staffno

SELECT M.staffno, M.forename, M.surname, P.postingid, P.duties
FROM master M INNER JOIN postings P ON M.staffno = P.staffno
WHERE P.posting_id IN ( @postingid_list )

I turned your outer join into an inner join, because you were doing that
anyway via your WHERE clause.

Regards,
Bill K.