Multiple Tables Last Record

[psi-phy]

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:

 

CLIENTS

-cli_id

-cli_fname

-cli_lname

 

NOTES

-note_id

-note_text

-note_dateentered

 

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:

 

CLI_ID - CLI_FNAME - CLI_LNAME - (MOST RECENT) NOTE_ID - (MOST RECENT) NOTE_TEXT

 

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...

[ryanlwh]

[!--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--]

[psi-phy]

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.

 

EDIT:

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.

[Katherine]

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.