Help with script design

[Andy-H]

I have these tables:

leads
   id
   title
   first_name
   last_name
   telephone
   mobile
   email
caravans
   id
   owner
   make
   model
   caravan_type
   axle
   CRiS
quote_data
   id
   vehicleID
   quote_reference
   company_name
   value
   has_tracker
   has_alarm
   fitted_device
   location
   date_quoted
   cover_start_date

 

 

I want to be able to display all leads with multiple quotes attached to the lead, and also need the ability to search by value, any customer details, has_tracker and has_alarm but I can't get one query to do this.

 

 

This is my current query, but the order by isn't operating as expected, and it just returns the first quote regardless of weather there's a newer one.

 

 

SELECT 
      l.id, l.title, l.first_name, l.last_name, l.telephone, l.mobile, l.email,
      c.make, c.model,
      qr.value, 
      IF(qr.has_tracker, 'Yes', 'No') AS has_tracker,
      IF(qr.has_alarm, 'Yes', 'No')   AS has_alarm,
      qr.date_quoted
   FROM leads l
   LEFT JOIN caravans c    ON ( l.id = c.owner )
   LEFT JOIN quote_data qr ON ( c.id = qr.vehicleID )
   GROUP BY l.email
   ORDER BY qr.id DESC

 

 

My database relations are :

leads - vehicle (linked via id - owner) 1 to many
vehicle - quote_data (linked via  id - vehicleID) 1 to many

Is there any way I can retrieve leads, with all vehicles linked to them and all quotes linked to each vehicle in one query, or any way to do this and allow searching as mentioned above, without writing spaghetti code?

[DavidAM]

The GROUP BY phrase is used to limit the results to a single row for each value of GROUP BY field(s). It is intended for use with aggregate functions. For instance:

 

SELECT l.email, COUNT(q.id), MAX(q.value), MIN(q.value) 
FROM ...
GROUP BY l.email

would give you the Count, Maximum Value and Minimum Value of all quotes for each lead.

 

The ORDER BY (which appears after the GROUP BY) controls the sequence of the returned rows and is not affected by the GROUP BY at all.

 

In your case, I think you want to remove the GROUP BY, and move the field you have there into the (beginning of) the ORDER BY.

 

SELECT 
   ...
   FROM leads l
   ...
   ORDER BY l.email, qr.id DESC