Jump to content


Photo

Multiple Tables Last Record


  • Please log in to reply
3 replies to this topic

#1 psi-phy

psi-phy
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 06 October 2005 - 08:16 PM

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

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 06 October 2005 - 08:42 PM

[!--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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 psi-phy

psi-phy
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 06 October 2005 - 09:16 PM

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.

#4 Katherine

Katherine
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 October 2005 - 02:14 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users