DISTINCT for two fields in JOIN

[sKunKbad]

I've got a query that may produce multiple results because of multiple tenants records, but I need to have only one result per unit:

 

SELECT 
DISTINCT u.`unit-id`, 
u.`street-address`, 
u.`property-id-fk`, 
u.`suite-no`, 
t.`tenant-id`
FROM unit_table u
LEFT JOIN tenant_table t
ON u.`unit-id` = t.`unit-id-fk`
WHERE u.`status` = 'Inactive' 
AND u.`owner-id-fk` = ?

 

This doesn't work unless I dont select tenant-id, but I need a tenant-id. If I put DISTINCT before t.`tenant-id` it produces an error. How can I have the query only return a single row for each unit AND tenant?

[trq]

You want to GROUP BY, not DISTINCT.

[fenway]

In fact, never use DISTINCT.