SQL Left Join - Multiple records returned from joined tables

[StefIsScared]

I have the following tables (simplified):

Clients
client_id
client_name

Notes
note_id
note_text
note_date
client_id

Products Purchased
purchase_id
product_id
client_id

A client can only exist once in Clients, but may have multiple notes and purchases. I am trying to get a list of clients and only the most recent product and the date of the most recent note. I thought I could do this with a left join along the lines of ... FROM Clients LEFT JOIN Notes USING(client_id) LEFT JOIN Products Purchased USING(client_id) but that will return multiple rows - for example if there are 5 notes it returns all 5. I can't just do a limit 0,1 etc. as I'd like to get a list of all clients - ideally without looping through and doing individual queries.

I think I am really missing something very simple here?

 

[r937]

I am trying to get a list of clients and only the most recent product and the date of the most recent note"

okay, the most recent note is easy

but how would you know which one is the most recent product?

r937.com | rudy.ca

 

[StefIsScared]

Hi Rudy,

Thanks for the prompt response.

note_id is an auto_increment primary key, it would be the highest value. The date of the product purchase is irrelevant.

 

[r937]

select C.client_name
     , N.note_text
     , P.product_id
  from Clients as C
left outer
  join Notes as N
    on N.client_id = C.client_id
   and N.note_date
     = ( select max(note_date)
           from Notes
          where client_id = C.client_id )
left outer
  join `Products Purchased` as P
    on P.client_id = C.client_id
   and P.purchase_id
     = ( select max(purchase_id)
           from `Products Purchased` 
          where client_id = C.client_id )

r937.com | rudy.ca