[RESOLVED] An Inner Join with a Sub-Query ?

Marc_McIntire

Hello SQL Gurus,

I am trying to form a MySQL query (version 4.1) that will return all of the clients and their most recent certificate info from the certificates table, by using max(exp_date) which is the date that their certificate expires. It is a one to many relationship, as each client can have may certificates, but I only want to see the info of the one with the latest exp_date.

I have tried several different forms of the query with only partial success. I believe that it will probably require a sub-query but I have not been able to get it formed properly. I have not written many sub-queries and have not been able to get it right.

Here is my latest attempt not using a sub-query that unfortunately also does not work. It pulls all of the records, and the certificate info, but it does not get the certificate with the latest exp_date.

SELECT
certs.exp_date,
certs.cert_no,
clients.client_id,
clients.state_lic,
clients.o_fname,
clients.o_lname,
clients.bus_name
FROM comm_certs certs
Inner Join comm_clients clients ON certs.client_id = clients.client_id
GROUP BY certs.client_id
HAVING MAX(certs.exp_date)
ORDER BY clients.client_id ASC

Any assistance would be very much appreciated.

Thank you,

Marc McIntire

mborish_2000

Try this:

SELECT
certs.cert_no,
clients.client_id,
clients.state_lic,
clients.o_fname,
clients.o_lname,
clients.bus_name
FROM comm_certs certs
Inner Join comm_clients clients ON certs.client_id = clients.client_id
GROUP BY certs.client_id =
(SELECT certs.exp_date ORDER BY certs.exp_date DESC)
ORDER BY clients.client_id ASC

Marc_McIntire

Hello mborish_2000,

This query only returns one record, and I can not seem to tell exactly why it returned the one that it did.

Thank you for the reply.

Marc

mborish_2000

SELECT
certs.exp_date,
certs.cert_no,
clients.client_id,
clients.state_lic,
clients.o_fname,
clients.o_lname,
clients.bus_name
FROM comm_certs certs
Inner Join comm_clients clients ON certs.client_id = clients.client_id
GROUP BY certs.client_id
ORDER BY clients.client_id ASC, certs.exp_date DESC;

Marc_McIntire

Hello mborish_2000,

This query gets all of the rows, but not the newest exp_date. The results were exactly identical to the original query.

Marc

mborish_2000

I'm sure you're avoiding this, but you should probably normalize the database. It would be alot easier to work with. I'm not the greatest at this stuff either.

Marc_McIntire

Hmmmm... I don't think that I can normalize the database any further. I have the clients in one table and their certificates in another table related by client_id.

Any specific suggestions regarding that?

I think that the answer actually lies in a sub-query to retreive the latest exp_date for the join. I just wish that I knew a bit more about forming a proper sub-query. I am Googling now and trying to educate myself on that.

Any additional assistance would be most appreciated.

Thank you again.

Marc

Marc_McIntire

Hey everyone,

I still do not have a resolution for this SQL query. Any additional advice or ideas would be most appreciated.

I have been researching in the area of sub-queries, but from what I have found, a sub-query can only have a maximum of one column. But again, I am still looking into that.

Any takers? Please... ?

Marc

Lars_Berg

SELECT
certs.exp_date,
certs.cert_no,
clients.client_id,
clients.state_lic,
clients.o_fname,
clients.o_lname,
clients.bus_name
FROM comm_certs certs
Inner Join comm_clients clients ON certs.client_id = clients.client_id
where certs.exp_date =
 (select max(exp_date)
    from comm_certs
   where certs.client_id = comm_certs.client_id)
ORDER BY clients.client_id ASC

Marc_McIntire

Lars,

Your query works perfectly! Thank you very much for taking the time; I really do appreciate it.

Have a great day!

Marc

mAGOO

Just wondering if someone could explain this code a little. This is exaclty what I need and I have tried it but for some reason it's not working.

My situation is as follows:

I have two tables, one for user details and another for their reports filed.

I have used it with my variables inserted, but I keep getting an SQL error on the "max".

I'm also a little confused about the comm_clients and comm_cert. Where are these from? I assume they are aliasses used to refer back to itself, but not totally sure.

My table names are pilots and reports.

SELECT *
FROM comm_reports reports
INNER JOIN comm_pilots pilots ON reports.id = pilots.id
WHERE reports.adminEdit = 
(SELECT MAX(adminEdit)
FROM comm_reports
WHERE reports.id = comm_reports.id)
GROUP BY report.id ASC

Hope someone can shed some light on this for me.

Cheers
Steph.