CODEWITHSUNDEEP
mysql
Michael LB
Michael LB

Reputation: 2722

MySQL Left Join on Max Value

In the table calendar_event there can be several calendar events (inspections). At present my query returns the oldest inspection (oldest if we rank them by the field ce.calendar_event_start), I want it to return the newest (latest) inspection.

I'm struggling with the approach to this. I've tried adding MAX around ce.calendar_event_start but that doesn't work. I'm assuming I have to change the LEFT JOIN but I can't figure out how I'd change it so that it's joining on the newest ce.calendar_event_start value.

UPDATE 1 - Thanks to @Vinit

Changed the LEFT JOIN as per the below but the query fails to execute;

LEFT JOIN calendar_event ce
on ce.calendar_event_tenancy = t.tenancy_id 
AND ce.calendar_event_id = (SELECT calendar_event_id FROM
                                calendar_event ceInner
                                ceInner.calendar_event_tenancy = t.tenancy_id
                                AND ce.calendar_event_type='7' 
                                AND ce.calendar_event_status!='3'
                                ORDER BY ceInner.calendar_event_start DESC)

UPDATE 2 - I worked on @Vinit answer to come up with the below that now works! Could this be improved any further?

Changed the LEFT JOIN as per the below but the query fails to execute;

LEFT JOIN calendar_event ce
on ce.calendar_event_tenancy = t.tenancy_id AND ce.calendar_event_id = (SELECT ceInner.calendar_event_id FROM
                                calendar_event ceInner
                                WHERE ceInner.calendar_event_tenancy = t.tenancy_id
                                AND ceInner.calendar_event_type='7' 
                                AND ceInner.calendar_event_status!='3'
                                ORDER BY ceInner.calendar_event_start DESC
                                LIMIT 1)

UPDATE 3 - I've noticed the query takes a very long time to execute (approx 10 seconds). Is there a more efficient version of the same thing? Yes there is! See below;

LEFT JOIN calendar_event ce
on ce.calendar_event_tenancy = t.tenancy_id AND ce.calendar_event_start  = (SELECT MAX(ceInner.calendar_event_start) FROM
                                calendar_event ceInner
                                WHERE ceInner.calendar_event_tenancy = t.tenancy_id
                                AND ceInner.calendar_event_type='7' 
                                AND ceInner.calendar_event_status!='3'
                                AND ceInner.calendar_event_inspection_type!='2')

Query;

SELECT t.*, ce.*, IF(ce.calendar_event_start IS NOT NULL, ce.calendar_event_start, t.tenancy_start_date) AS LastInspection
FROM tenancy t
LEFT JOIN calendar_event ce
on ce.calendar_event_tenancy = t.tenancy_id AND ce.calendar_event_type='7' AND ce.calendar_event_status!='3'
ORDER BY LastInspection ASC

Tables & Sample Data;

-- phpMyAdmin SQL Dump
-- version 4.7.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jul 19, 2018 at 07:57 PM
-- Server version: 5.6.39
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `propsyst_main`
--

-- --------------------------------------------------------

--
-- Table structure for table `calendar_event`
--

CREATE TABLE `calendar_event` (
  `calendar_event_id` int(11) NOT NULL,
  `calendar_event_company_id` int(11) DEFAULT NULL,
  `calendar_event_branch_id` int(11) DEFAULT NULL,
  `calendar_event_subject` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `calendar_event_status` tinyint(4) DEFAULT NULL,
  `calendar_event_start` datetime DEFAULT NULL,
  `calendar_event_end` datetime DEFAULT NULL,
  `calendar_event_location` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `calendar_event_type` tinyint(4) DEFAULT NULL,
  `calendar_event_employee` int(11) DEFAULT NULL,
  `calendar_event_description` text COLLATE utf8_bin,
  `calendar_event_attendee_type` int(11) DEFAULT NULL,
  `calendar_event_property` int(11) DEFAULT NULL,
  `calendar_event_tenancy` int(11) DEFAULT NULL,
  `calendar_event_applicant` int(11) DEFAULT NULL,
  `calendar_event_valuation` int(11) DEFAULT NULL,
  `calendar_event_meet_at` tinyint(4) DEFAULT NULL,
  `calendar_event_date_created` datetime DEFAULT NULL,
  `calendar_event_date_updated` datetime DEFAULT NULL,
  `calendar_event_created_by` int(11) DEFAULT NULL,
  `calendar_event_updated_by` int(11) DEFAULT NULL,
  `calendar_event_feedback_position` text COLLATE utf8_bin,
  `calendar_event_feedback_pros` text COLLATE utf8_bin,
  `calendar_event_feedback_cons` text COLLATE utf8_bin,
  `calendar_event_feedback_confidence_level` tinyint(4) DEFAULT NULL,
  `calendar_event_feedback_public_comments` text COLLATE utf8_bin,
  `calendar_event_feedback_private_comments` text COLLATE utf8_bin,
  `calendar_event_confirmed_landlord_vendor` tinyint(4) DEFAULT NULL,
  `calendar_event_confirmed_applicant` tinyint(4) DEFAULT NULL,
  `calendar_event_notes` text COLLATE utf8_bin,
  `calendar_event_private_notes` text COLLATE utf8_bin,
  `calendar_event_branch` int(11) DEFAULT NULL,
  `calendar_event_recurring_id` int(11) DEFAULT NULL,
  `calendar_event_cancellation_reason` text COLLATE utf8_bin,
  `calendar_event_surveyor_company_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `calendar_event_surveyor_individual_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `calendar_event_surveyor_phone_number` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `calendar_event_directory` int(11) DEFAULT NULL,
  `calendar_event_inspection_type` tinyint(1) DEFAULT NULL,
  `calendar_event_survey_type` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `calendar_event`
--

INSERT INTO `calendar_event` (`calendar_event_id`, `calendar_event_company_id`, `calendar_event_branch_id`, `calendar_event_subject`, `calendar_event_status`, `calendar_event_start`, `calendar_event_end`, `calendar_event_location`, `calendar_event_type`, `calendar_event_employee`, `calendar_event_description`, `calendar_event_attendee_type`, `calendar_event_property`, `calendar_event_tenancy`, `calendar_event_applicant`, `calendar_event_valuation`, `calendar_event_meet_at`, `calendar_event_date_created`, `calendar_event_date_updated`, `calendar_event_created_by`, `calendar_event_updated_by`, `calendar_event_feedback_position`, `calendar_event_feedback_pros`, `calendar_event_feedback_cons`, `calendar_event_feedback_confidence_level`, `calendar_event_feedback_public_comments`, `calendar_event_feedback_private_comments`, `calendar_event_confirmed_landlord_vendor`, `calendar_event_confirmed_applicant`, `calendar_event_notes`, `calendar_event_private_notes`, `calendar_event_branch`, `calendar_event_recurring_id`, `calendar_event_cancellation_reason`, `calendar_event_surveyor_company_name`, `calendar_event_surveyor_individual_name`, `calendar_event_surveyor_phone_number`, `calendar_event_directory`, `calendar_event_inspection_type`, `calendar_event_survey_type`) VALUES
(5170, 100, 0, 'Property Inspection (Rent Arrears) with Ms xxx xxx at Apartment 1 1 Howard Drive, Aigburth, Liverpool, L19 XXX', 2, '2017-07-28 14:30:00', '2017-07-28 14:45:00', 'Apartment 1 1 Howard Drive, Aigburth, Liverpool, L19 XXX', 7, 1, 'Tenant(s): Ms xxx xxx (Mobile: 00000000000, Email: [email protected])\r\nMeeting with: Agent (Michael Le Brocq)\r\nInspection Type: Rent Arrears\r\nPublic Notes: Property Inspection due to late rent\r\nStatus: Confirmed\r\nOriginally Arranged: 28/07/17 11:45:19 by Jane Nicholson\r\nLast Updated: 19/07/18 16:00:59 by Michael Le Brocq', 1, 172, 82, 0, 0, 0, '2017-07-28 11:45:19', '2018-07-19 16:00:59', 33, 1, '', NULL, NULL, NULL, NULL, NULL, 0, 0, 'Property Inspection due to late rent', '', 1, NULL, '', '', '', '', 0, 2, 0),
(5931, 100, 0, 'Property Inspection (Rent Arrears) with Ms Susan XXX at Apartment 1 1 Howard Drive, Aigburth, Liverpool, L19 XXX', 2, '2017-10-30 12:00:00', '2017-10-30 12:30:00', 'Apartment 1 1 Howard Drive, Aigburth, Liverpool, L19 XXX', 7, 1, 'Tenant(s): Ms Susan XXX (Mobile: 00000000000, Email: [email protected])\r\nMeeting with: Agent (Michael Le Brocq)\r\nInspection Type: Rent Arrears\r\nPublic Notes: Tenant not paid the rent\r\nStatus: Confirmed\r\nOriginally Arranged: 25/10/17 13:11:32 by Jane Nicholson\r\nLast Updated: 19/07/18 16:02:50 by Michael Le Brocq', 1, 172, 82, 0, 0, 0, '2017-10-25 13:11:32', '2018-07-19 16:02:50', 33, 1, '', NULL, NULL, NULL, NULL, NULL, 0, 0, 'Tenant not paid the rent', '', 1, NULL, '', '', '', '', 0, 2, 0),
(8929, 100, 0, 'Property Inspection (Routine) with Ms Susan XXX at Apartment 1 1 Howard Drive, Aigburth, Liverpool, L19 XXX', 2, '2018-07-25 15:00:00', '2018-07-25 15:15:00', 'Apartment 1 1 Howard Drive, Aigburth, Liverpool, L19 XXX', 7, 36, 'Tenant(s): Ms Susan XXX (Mobile: 00000000000, Email: [email protected])\r\nMeeting with: Agent (Jan Borrows)\r\nInspection Type: Routine\r\nPublic Notes: Confirmed with Diane\r\nStatus: Confirmed\r\nOriginally Arranged: 17/07/18 15:37:54 by Jan Borrows\r\nLast Updated: 17/07/18 15:37:54 by Jan Borrows', 1, 172, 82, 0, 0, 0, '2018-07-17 15:37:54', '2018-07-17 15:37:54', 36, 36, '', NULL, NULL, NULL, NULL, NULL, 0, 0, 'Confirmed with Diane', '', 1, NULL, NULL, '', '', '', 0, 1, 0);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `calendar_event`
--
ALTER TABLE `calendar_event`
  ADD PRIMARY KEY (`calendar_event_id`),
  ADD UNIQUE KEY `calendar_event_id` (`calendar_event_id`),
  ADD KEY `calendar_event_status` (`calendar_event_status`),
  ADD KEY `calendar_event_start` (`calendar_event_start`),
  ADD KEY `calendar_event_end` (`calendar_event_end`),
  ADD KEY `calendar_event_property` (`calendar_event_property`),
  ADD KEY `calendar_event_employee` (`calendar_event_employee`),
  ADD KEY `calendar_event_type` (`calendar_event_type`),
  ADD KEY `calendar_event_company_id` (`calendar_event_company_id`),
  ADD KEY `calendar_event_branch_id` (`calendar_event_branch_id`),
  ADD KEY `calendar_event_attendee_type` (`calendar_event_attendee_type`),
  ADD KEY `calendar_event_applicant` (`calendar_event_applicant`),
  ADD KEY `calendar_event_valuation` (`calendar_event_valuation`),
  ADD KEY `calendar_event_meet_at` (`calendar_event_meet_at`),
  ADD KEY `calendar_event_branch` (`calendar_event_branch`),
  ADD KEY `calendar_event_recurring_id` (`calendar_event_recurring_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `calendar_event`
--
ALTER TABLE `calendar_event`
  MODIFY `calendar_event_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8968;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

AND

-- phpMyAdmin SQL Dump
-- version 4.7.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jul 19, 2018 at 07:58 PM
-- Server version: 5.6.39
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `propsyst_main`
--

-- --------------------------------------------------------

--
-- Table structure for table `tenancy`
--

CREATE TABLE `tenancy` (
  `tenancy_id` int(11) NOT NULL,
  `tenancy_token` varchar(40) COLLATE utf8_bin DEFAULT NULL,
  `tenancy_type` tinyint(4) DEFAULT NULL,
  `tenancy_property` int(11) DEFAULT NULL,
  `tenancy_furnished` int(11) DEFAULT NULL,
  `tenancy_children` tinyint(4) DEFAULT NULL,
  `tenancy_pets` tinyint(4) DEFAULT NULL,
  `tenancy_smoking` tinyint(4) NOT NULL,
  `tenancy_property_part` tinyint(4) DEFAULT NULL,
  `tenancy_property_part_room` int(11) DEFAULT NULL,
  `tenancy_property_part_description` text COLLATE utf8_bin,
  `tenancy_shared_facilities` tinyint(4) DEFAULT NULL,
  `tenancy_shared_facilities_description` text COLLATE utf8_bin,
  `tenancy_agreement_date` date DEFAULT NULL,
  `tenancy_start_date` date DEFAULT NULL,
  `tenancy_fixed_term` smallint(6) DEFAULT NULL,
  `tenancy_fixed_term_unit` tinyint(4) DEFAULT NULL,
  `tenancy_fixed_term_end_date` date DEFAULT NULL,
  `tenancy_rent_amount` decimal(8,2) DEFAULT NULL,
  `tenancy_rent_frequency` int(11) DEFAULT NULL,
  `tenancy_rent_payable` int(11) DEFAULT NULL,
  `tenancy_rent_agreement` int(11) DEFAULT NULL,
  `tenancy_rent_frequency_schedule` text CHARACTER SET utf8,
  `tenancy_rent_payment_method` tinyint(4) DEFAULT NULL,
  `tenancy_council_pay_rent` tinyint(4) DEFAULT NULL,
  `tenancy_rent_vat_rate` tinyint(4) DEFAULT NULL,
  `tenancy_service_charge_amount` decimal(8,2) DEFAULT NULL,
  `tenancy_service_charge_frequency` int(11) DEFAULT NULL,
  `tenancy_service_charge_payable` int(11) DEFAULT NULL,
  `tenancy_service_charge_agreement` int(11) DEFAULT NULL,
  `tenancy_service_charge_frequency_schedule` text COLLATE utf8_bin,
  `tenancy_service_charge_payment_method` tinyint(4) DEFAULT NULL,
  `tenancy_service_charge_vat_rate` tinyint(4) DEFAULT NULL,
  `tenancy_insurance_amount` decimal(8,2) DEFAULT NULL,
  `tenancy_insurance_frequency` int(11) DEFAULT NULL,
  `tenancy_insurance_payable` int(11) DEFAULT NULL,
  `tenancy_insurance_agreement` int(11) DEFAULT NULL,
  `tenancy_insurance_frequency_schedule` text COLLATE utf8_bin,
  `tenancy_insurance_payment_method` tinyint(4) DEFAULT NULL,
  `tenancy_insurance_vat_rate` tinyint(4) DEFAULT NULL,
  `tenancy_notes` text COLLATE utf8_bin,
  `tenancy_agent_branch` int(11) DEFAULT NULL,
  `tenancy_agent_employee` int(11) DEFAULT NULL,
  `tenancy_letting_service` tinyint(4) DEFAULT NULL,
  `tenancy_tenant_find_fee` decimal(10,2) DEFAULT NULL,
  `tenancy_tenant_find_fee_type` tinyint(4) DEFAULT NULL,
  `tenancy_management_fee` decimal(10,2) DEFAULT NULL,
  `tenancy_management_fee_type` tinyint(4) DEFAULT NULL,
  `tenancy_tenant_fee` decimal(6,2) DEFAULT NULL,
  `tenancy_guarantor_fee` decimal(6,2) DEFAULT NULL,
  `tenancy_reminder_letter_fee` decimal(6,2) DEFAULT NULL,
  `tenancy_missed_payment_fee` decimal(6,2) DEFAULT NULL,
  `tenancy_gas` tinyint(4) DEFAULT NULL,
  `tenancy_electricity` tinyint(4) DEFAULT NULL,
  `tenancy_water` tinyint(4) DEFAULT NULL,
  `tenancy_oil` tinyint(4) DEFAULT NULL,
  `tenancy_telephone` tinyint(4) DEFAULT NULL,
  `tenancy_broadband` tinyint(4) DEFAULT NULL,
  `tenancy_tv_licence` tinyint(4) DEFAULT NULL,
  `tenancy_sat_cable_tv` tinyint(4) DEFAULT NULL,
  `tenancy_council_tax` tinyint(4) DEFAULT NULL,
  `tenancy_service_charge` tinyint(4) DEFAULT NULL,
  `tenancy_ground_rent` tinyint(4) DEFAULT NULL,
  `tenancy_deposit_required` tinyint(4) DEFAULT NULL,
  `tenancy_deposit_amount` decimal(8,2) DEFAULT NULL,
  `tenancy_deposit_protection_responsible` tinyint(4) DEFAULT NULL,
  `tenancy_deposit_protection_scheme` tinyint(4) DEFAULT NULL,
  `tenancy_status` tinyint(4) DEFAULT NULL,
  `tenancy_renewal_status` tinyint(4) DEFAULT '4',
  `tenancy_renewal_notes` text COLLATE utf8_bin,
  `tenancy_move_out_date` date DEFAULT NULL,
  `tenancy_move_out_reason` tinyint(4) DEFAULT NULL,
  `tenancy_move_out_notes` text COLLATE utf8_bin,
  `tenancy_tenant_find_with_management_fee` decimal(7,2) DEFAULT NULL,
  `tenancy_tenant_find_with_management_fee_type` tinyint(4) DEFAULT NULL,
  `tenancy_overdue_tc_reminders` tinyint(4) NOT NULL DEFAULT '1',
  `tenancy_student` tinyint(4) DEFAULT NULL,
  `tenancy_inspection_frequency` tinyint(4) DEFAULT NULL,
  `tenancy_move_out_employee` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `tenancy`
--

INSERT INTO `tenancy` (`tenancy_id`, `tenancy_token`, `tenancy_type`, `tenancy_property`, `tenancy_furnished`, `tenancy_children`, `tenancy_pets`, `tenancy_smoking`, `tenancy_property_part`, `tenancy_property_part_room`, `tenancy_property_part_description`, `tenancy_shared_facilities`, `tenancy_shared_facilities_description`, `tenancy_agreement_date`, `tenancy_start_date`, `tenancy_fixed_term`, `tenancy_fixed_term_unit`, `tenancy_fixed_term_end_date`, `tenancy_rent_amount`, `tenancy_rent_frequency`, `tenancy_rent_payable`, `tenancy_rent_agreement`, `tenancy_rent_frequency_schedule`, `tenancy_rent_payment_method`, `tenancy_council_pay_rent`, `tenancy_rent_vat_rate`, `tenancy_service_charge_amount`, `tenancy_service_charge_frequency`, `tenancy_service_charge_payable`, `tenancy_service_charge_agreement`, `tenancy_service_charge_frequency_schedule`, `tenancy_service_charge_payment_method`, `tenancy_service_charge_vat_rate`, `tenancy_insurance_amount`, `tenancy_insurance_frequency`, `tenancy_insurance_payable`, `tenancy_insurance_agreement`, `tenancy_insurance_frequency_schedule`, `tenancy_insurance_payment_method`, `tenancy_insurance_vat_rate`, `tenancy_notes`, `tenancy_agent_branch`, `tenancy_agent_employee`, `tenancy_letting_service`, `tenancy_tenant_find_fee`, `tenancy_tenant_find_fee_type`, `tenancy_management_fee`, `tenancy_management_fee_type`, `tenancy_tenant_fee`, `tenancy_guarantor_fee`, `tenancy_reminder_letter_fee`, `tenancy_missed_payment_fee`, `tenancy_gas`, `tenancy_electricity`, `tenancy_water`, `tenancy_oil`, `tenancy_telephone`, `tenancy_broadband`, `tenancy_tv_licence`, `tenancy_sat_cable_tv`, `tenancy_council_tax`, `tenancy_service_charge`, `tenancy_ground_rent`, `tenancy_deposit_required`, `tenancy_deposit_amount`, `tenancy_deposit_protection_responsible`, `tenancy_deposit_protection_scheme`, `tenancy_status`, `tenancy_renewal_status`, `tenancy_renewal_notes`, `tenancy_move_out_date`, `tenancy_move_out_reason`, `tenancy_move_out_notes`, `tenancy_tenant_find_with_management_fee`, `tenancy_tenant_find_with_management_fee_type`, `tenancy_overdue_tc_reminders`, `tenancy_student`, `tenancy_inspection_frequency`, `tenancy_move_out_employee`) VALUES
(82, 'aJ9GCrxk9IEkBkLUqxsTuIdlbHnGNXTc4coq69AW', 1, 172, 2, 0, 0, 0, 1, 0, '', 0, '', '2014-08-21', '2014-08-21', 6, 2, '2015-02-20', '395.00', 3, 3, 1, '', 3, 0, 0, '0.00', 0, 0, 0, '', 0, 0, '0.00', 0, 0, 0, '', 0, 0, '', 1, 1, 3, '225.00', 1, '10.00', 2, '70.00', '50.00', '5.00', '15.00', 2, 2, 2, 3, 2, 2, 2, 2, 2, 3, 3, 1, '500.00', 1, 1, 1, 1, NULL, '0000-00-00', 0, '', '239.00', 1, 1, 0, 12, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tenancy`
--
ALTER TABLE `tenancy`
  ADD PRIMARY KEY (`tenancy_id`),
  ADD UNIQUE KEY `tenancy_token` (`tenancy_token`),
  ADD KEY `tenancy_id` (`tenancy_id`),
  ADD KEY `tenancy_property` (`tenancy_property`),
  ADD KEY `tenancy_status` (`tenancy_status`),
  ADD KEY `tenancy_letting_service` (`tenancy_letting_service`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tenancy`
--
ALTER TABLE `tenancy`
  MODIFY `tenancy_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=526;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Upvotes: 1

Views: 150

Answers (1)

Vinit
Vinit

Reputation: 2607

The order by on date column suppose to be descending to get latest inspection date

ORDER BY LastInspection DESC

EDIT You may try this query -

SELECT t.*, 
ce.*, 
IF(
   ce.calendar_event_start IS NOT NULL, 
   ce.calendar_event_start, 
   t.tenancy_start_date
) AS LastInspection
FROM tenancy t
LEFT JOIN calendar_event ce
on ce.calendar_event_tenancy = t.tenancy_id 
    AND ce.calendar_event_start  = (select max(ceInner.calendar_event_start) from -- get the recent event id
                                calendar_event ceInner
                                ceInner.calendar_event_tenancy = t.tenancy_id
                                AND ceInner.calendar_event_type='7' 
                                AND ceInner.calendar_event_status!='3'
                                )

ORDER BY LastInspection DESC

Upvotes: 1

Related Questions