Jump to content


This topic is now archived and is closed to further replies.


Multiple Tables Last Record

Recommended Posts

Greetings all,


I cannot seem to figure out how to do this, so I turned to the forums. I have two tables, CLIENTS and NOTES. A CLIENT can have multiple NOTES. So I need to do a query that gives me the LAST note for each client. I then am putting that into a table.


(To complicate things, I need to order the client data by last name too....)


I thought I could do it with a sub-query, but I very well could be doing it wrong. I tried this:


SELECT * FROM 'notes' n, 'clients' c WHERE c.cli_id = n.note_cliid AND ??? (SELECT * FROM notes { FOR THE SPECIFIED CLIENT ID } ORDER BY note_id DESC LIMIT 1)


An example of the tables is:












I'm not sure if I am in the right direction. The CLIENTS table has client data, the NOTES table has multiple notes for any given client, so the query should return:




etc, etc... and there is a PHP FOR loop there to get this data for each CLIENT.


Any thoughts? I'm fresh out of them... :)

Share this post

Link to post
Share on other sites

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM `notes` n, `clients` c WHERE c.cli_id=n.note_cliid AND c.cli_id={specified cli_id} ORDER BY note_id DESC, cli_lname ASC LIMIT 1 [!--sql2--][/div][!--sql3--]

Share this post

Link to post
Share on other sites

Cool, that does what I would need, however for

c.cli_id={specified cli_id}


What if the "specified cli_id" needs to come FROM the database in that query? I noticed I can change "specified cli_id" to say 1, and I get the info I need for client 1. The idea is to have the page load in a table where each row is a clients name and such and then at the end of the row, there is a recent note.



I have been trying some things using the actual info from my DB. The query as I have it now is this:

SELECT cli_id, wpn_id, cli_fname, cli_lname, wpn_edit_date
FROM `progress_notes` p, `clients` c
WHERE c.cli_id=p.wpn_cliid
ORDER BY wpn_id DESC, cli_lname ASC

Now that gives me what I need, except it gives me all notes. I just need the last one for each respective cli_id. So I tried using the "LIMIT 1" and then I just get the last note overall. I assumed due to the nature of HOW I need the data, a sub-query is necessary, however as this little project I am on is an ever-learning process, I am not horrendously sure on the proper way to do it.

Share this post

Link to post
Share on other sites

Well you need a GROUP BY somewhere

With something like:


SELECT cli_id, wpn_id, cli_fname, cli_lname, max(wpn_edit_date)

FROM `progress_notes` p, `clients` c

GROUP BY cli_id, wpn_id, cli_fname, cli_lname

WHERE c.cli_id=p.wpn_cliid

ORDER BY wpn_id DESC, cli_lname ASC


You need to figure a criteria for deciding which note is the most recent.

Here I assume the max(wpn_edit_date) will do the trick. Possibly you need to choose another fields depending on your table structure.

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.