Use of Subquery in a query

Question

I have a mysql database which has a number of tables:

• matters

  1. matterid
  2. mattername
  3. state

• matterjuncstaff

  1. junked
  2. matterid
  3. staffid

• staff

  1. staffid
  2. staffname
  3. lead (lead can be either Y or N)

• matterjunactions

  1. matterid
  2. actiondate

Rules

• A matter will have a number of staff associated with it but only one lead.
• A matter can have any number of actions associated with it.

I want to extract a table which shows all matters where their state is 'Open' and lists the matterid, mattername, the staffname for the person who is the lead on the matter (i.e. has lead='Y') and the last actiondate for the matter.

I have tried various ways of doing this and believe that it can be done using subqueries but I can’t work out the format for getting these into my query.

Answer 1

It would look like this:

Select m.matterid, m.mattername, s.staffname, max(actiondate)
From matters m
Inner join matterjuncstaff mjs using (matterid)
Inner join staff s using (staffid)
Inner join matterjunactions mj
 On mj.Matterid = m.matterid
Where m.state = 'open'
And s.lead = 'Y'
Group by m.matterid, m.mattername, s.staffname