Two table query

[TapeGun007]

I’ve used SQL for years, but only very simple one table queries.

 

I have two tables Prospect and Notes:

 

Prospects

                ProspectID

                ProspectName

                ProspectCode

                ProspectStatus

                …

 

Notes

                NoteID

                NoteProspectID

                Note

 

Here is what I want, but I don’t know how to write it in MySQL

 

All prospects with the following

ProspectCode = “123abc”

ProspectStatus = “Contacted”

 

I also want only the last Note from Notes where ProspectID = NoteProspectID.

 

I hope this makes sense.

  

[Psycho]

Barand may come up with something more efficient. But, give this a try:

 

 

SELECT p.ProspectID, p.ProspectName, p.ProspectCode, p.ProspectStatus,
       n.NoteID, n.Note
 
FROM Prospects p
LEFT JOIN Notes n
  ON p.ProspectID = n.NoteProspectID
  AND n.NoteID IN ( SELECT MAX(NoteID) FROM Notes GROUP BY NoteProspectID )
 
WHERE p.ProspectCode = “123abc”
  AND p.ProspectStatus = “Contacted”

[TapeGun007]

EDIT:

 

Yes, this does work.  I had posted that it did not, but it was because I didn't realize most of my "contacted" leads in the db were marked as something else.

[TapeGun007]

This also helped me greatly because I had a misunderstanding about using the WHERE clause with a LEFT JOIN that confused me greatly.

[Barand]

 

Barand may come up with something more efficient. But, give this a try:

 

 

Challenge declined

 

One comment I would make is that it would be better to put a timestamp in the notes tables to identify the latest rather than relying on id sequence. (Treat ids purely as unique row identities used for internal relationships. A database should function the same if ids were allocated randomly instead of sequentially)

[TapeGun007]

Good idea, and I do have a timestamp on each note already.