Getting a record with a MAX value across several join tables

user_12469951 0
Viewed 30

Schema: The schema for the question.

enter image description here

Goal (assuming this schema must remain the same):

What is the most recent outreach for the first salesperson created per company?

  1. For each record in the company table, get the corresponding record in the company_salesperson table using a matching company_salesperson.customer_id value and the MAX(company_salesperson.created_at) value.
  • {For the company record w/ id == 1: this should return company_salesperson record w/ id == 2}
  1. For each returned company_salesperson record, get all company_salesperson_company_outreach records with a matching company_salesperson_company_outreach.company_salesperson_id value
  • For the company_salesperson record w/ id == 2, this should return two company_salesperson_company_outreach records w/ id's 2 and 3
  1. For each set of returned company_salesperson_company_outreach records, get the corresponding company_outreach record withe the MAX(company_outreach.outreach_id)
  • For the company_salesperson_company_outreach records w/ id's 2 and 3, we should get corresponding company_outreach records w/ id's 2 and 3. Based on their company_outreach.outreach_id values, this should see that company_outreach record w/ id == 3 has a greater company_outreach.outreach_id value. Therefore this should return company_salesperson_company_outreach record w/ id == 3.

Desired Output:

SELECT company.id, company_salesperson.id, company_salesperson_company_outreach.id, company_outreach.id
FROM ...???
company.id company_salesperson.id company_salesperson_company_outreach.id company_outreach.id
1 2 3 3

I feel that there is a very elegant solution to this, but after three days of my brain melting out of my ears, I was advised to reach out here! Thank you for your time.

schema mysql sql
1 Answers

I think you should be able to get this result by using nested subqueries, e.g.:

SELECT c.company_id,
  csco.company_salesperson_id,
  csco.id,
  c.outreach_id
FROM company_outreach c
  INNER JOIN company_salesperson_company_outreach csco
    on c.id = csco.company_outreach_id
WHERE c.Id = (
  SELECT MAX(company_outreach_id)
  FROM company_salesperson_company_outreach
  WHERE company_salesperson_id = (
    SELECT MIN(id)
    FROM company_salesperson
    WHERE company_id = c.company_id
    AND created_at = (
      SELECT MIN(created_at)
      FROM company_salesperson
      WHERE company_id = c.company_id
    ) 
  )
)

Doesn't feel to be "very elegant" as you requested, but it's the best I was able to come up with.

user_138783250
Related