How to get ride of multiple rows, with this type of LEFT JOIN

[coupe-r]

Hi All,

 

I have a query that should only bring back 1 result, because there is only 1 tenant.  However, there are 2 lease records for this same tenant, but they have a deleteStatus = 1, which means they are deleted.  With this query, how can I make it accurate and only grab the 1 tenant?

 

 

SELECT t.tenant_id, t.firstname, t.lastname, t.status, p.property_id, p.address1, p.address2, l.lease_start, l.lease_end

 

FROM tenants t

 

LEFT JOIN properties p ON t.property_id = p.property_id

LEFT JOIN lease l ON t.tenant_id = l.tenant_id

 

WHERE t.client_id = '1' ORDER BY t.tenant_id ASC LIMIT 0, 15

[kickstart]

Hi

 

Do you mean you only have one current tenant record and all the others are marked as deleted? If so something like this:-

 

SELECT t.tenant_id, t.firstname, t.lastname, t.status, p.property_id, p.address1, p.address2, l.lease_start, l.lease_end
FROM tenants t
LEFT JOIN properties p ON t.property_id = p.property_id
LEFT JOIN lease l ON t.tenant_id = l.tenant_id AND l.deleteStatus = 0
WHERE t.client_id = '1' 
ORDER BY t.tenant_id ASC 
LIMIT 0, 15

 

If you want the last lease to start:-

 

SELECT t.tenant_id, t.firstname, t.lastname, t.status, p.property_id, p.address1, p.address2, l2.lease_start, l2.lease_end
FROM tenants t
LEFT JOIN properties p ON t.property_id = p.property_id
LEFT JOIN (SELECT tenant_id, MAX(lease_start) AS MaxLeaseStart FROM lease) l ON t.tenant_id = l.tenant_id
LEFT OUTER JOIN lease l2 ON l.tenant_id = l2.tenant_id AND l.MaxLeaseStart = l2.lease_start
WHERE t.client_id = '1' 
ORDER BY t.tenant_id ASC 
LIMIT 0, 15

 

All the best

 

Keith

[coupe-r]

Thanks man.  Adding l.deleteStatus = '0' seemed to work.  I now only get 1 result, instead of 2 duplicates.

 

Thanks again!!!