2. PHPRunner General questions
  3. [SOLVED] Trying to limit an outer join to return just 1 result per customer
This topic is locked
[SOLVED]

Trying to limit an outer join to return just 1 result per customer

 		6/2/2021 12:26:36 AM
PHPRunner General questions
DealerModules authorDevClub member

I have a MYSQL statement that returns a list of customers that have a status = "Installation".

I have a left outer join that pulls the contacts from another table "contacts".

My question is that I would like to only pull 1 contact per customer in my list.

My problem is that if I have more than one contact per customer then I get multiple lines in my list per customer.

To limit the list, I have tried using LIMIT 1 but my result is just one customer only. I need all the customers but just LIMIT one contact per customer.

Below is my mysql select statement. Any direction appreciated.

SELECT
invcustomers.id,
invcustomers.name,
invcustomers.address,
invcustomers.emailaddress,
invcustomers.city,
invcustomers.state,
invcustomers.zip,
invcustomers.dealer_id,
invcustomers.installation_start_date AS start_date,
invcustomers.installation_go_live_date AS go_live_date,
invcustomers.products_installed,
concat(first_name, ' ', last_name) AS contact,
contacts.phone1 AS cphone1,
invcustomers.number_scanners,
invcustomers.scheduled,
invcustomers.shipped_or_office_pickup,
invcustomers.reported_dt,
invcustomers.type_units,
invcustomers.billed_on_sheet,
contacts.email_address,
dealership_products_installed.abbreviation
FROM invcustomers
LEFT OUTER JOIN dealership_products_installed ON invcustomers.products_installed = dealership_products_installed.products_installed
LEFT OUTER JOIN contacts ON invcustomers.dealer_id = contacts.dealer_id
WHERE (invcustomers.status ='Installation')
ORDER BY invcustomers.installation_start_date
admin 6/2/2021

It should be possible. Check this post for inspiration.

The key is not to join with the second table directly but to do a subquery with GROUP BY and a function like MAX() that would only return a single record from the joined table.

Dalkeith 6/2/2021

Yes I have done this with SQL in SQL Server before..

MySQL solution looks like it will be similar to this.
Max or Minimum child value

DealerModules authorDevClub member 6/2/2021

Thanks for the direction.