Jump to content

Two table query


TapeGun007
Go to solution Solved by Psycho,

Recommended Posts

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.

  

Link to comment
Share on other sites

  • Solution

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”
Link to comment
Share on other sites

 

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)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.