Databases
StackOverflow2013
Postsdbo
POMySQL Statement with multi nested JOINs and Distinct Limited Ordering
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Statement with multi nested JOINs and Distinct Limited Ordering
    primarykey
    data
    text
    <p>I'm attempting to build a list of results based on three joins</p> <p>I have created a table of leads, as my sales team takes action on the leads they attach event note records to the leads. 1 lead can have many notes. each note has a timestamp and also a date/time field where they can set a future date in order to schedule call backs and appointments.</p> <p>I have no trouble building the list, with all my leads associated with their respective event notes, but what I want to do in this particular case is query a smaller list of leads that are associated with only the event note containing the "newest"/highest value in the date_time column.</p> <p>I've been digging about especially here on stack for the last couple days attempting to get the desired result from my statements. I get either all of the lead records with all of their associated event note records or I get 1, no matter what I utilize ( GROUP BY date_time ASC LIMIT 1) or (ORDER BY date_time ASC LIMIT 1) I've even tried to build a view with only the highest scheduled record for each lead.id.</p> <pre><code>SELECT rr_leads.id AS 'Lead', rr_leads.first, rr_leads.last, rr_leads.company, rr_leads.phone, rr_leads.email, rr_leads.city, rr_leads.zip, rr_leads.status, z.noteid, z.taskid, z.scheduled, z.event FROM rr_leads LEFT JOIN ( SELECT rr_lead_notes.lead_id, rr_lead_notes.id AS 'noteid', rr_lead_tasks.id AS 'taskid', rr_lead_notes.date_time AS 'scheduled', rr_lead_notes.task_note, rr_lead_tasks.task_step AS 'event' FROM rr_lead_notes LEFT JOIN rr_lead_tasks ON rr_lead_notes.task_note = rr_lead_tasks.task_step AND rr_lead_notes.id IS NOT NULL AND rr_lead_notes.task_note IS NOT NULL GROUP BY rr_lead_notes.id DESC ) z ON rr_leads.id = z.lead_id WHERE rr_leads.id IS NOT NULL AND z.noteid IS NOT NULL ORDER BY rr_leads.id DESC </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USIchimonban
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you asking for a list of leads with only their most recent note?
      singulars
      1. POMySQL Statement with multi nested JOINs and Distinct Limited Ordering
      1. USAgRizzo
    2. COThat's a lot of code. I suggest selecting two fields, plus the time_stamp, from 1 table. Order by the time_stamp desc and limit 1. Keep building it up until you stop getting the expected result.
      singulars
      1. POMySQL Statement with multi nested JOINs and Distinct Limited Ordering
      1. USDan Bracuk
    3. COI want to select each lead with only it's most recent/or future event note
      singulars
      1. POMySQL Statement with multi nested JOINs and Distinct Limited Ordering
      1. USIchimonban
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload