Clash Royale CLAN TAG#URR8PPP

Duplicate row in multi table join

I have four tables as below:

tenant
+----+------------+
| id | name |
+----+------------+
| 1 | John Smith |
| 2 | Anna Jones |
+----+------------+

property
+----+-------------+---------------+
| id | landlord_id | address |
+----+-------------+---------------+
| 1 | 1 | King Street 1 |
| 2 | 1 | Green Grove 2 |
| 3 | 2 | Queen Stree 3 |
+----+-------------+---------------+

tenant_has_property
+-----------+-------------+
| tenant_id | property_id |
+-----------+-------------+
| 1 | 1 |
| 1 | 2 |
+-----------+-------------+

landlord
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Best Homes Ltd. |
| 2 | RealEstates Inc |
+----+-----------------+


Now I would like to get a list of all tenants that rent a property from landlord id = 1

When I run a query like below:

SELECT
tenant.id, tenant.name
FROM
tenant, property, tenant_has_property
WHERE
tenant.id = tenant_has_property.tenant_id AND
tenant_has_property.property_id = property.id AND
property.landlord_id = 1


I am getting duplicate rows:

+----+------------+
| id | name |
+----+------------+
| 1 | John Smith |
| 1 | John Smith |
+----+------------+


I know changing the query to

SELECT
DISTINCT tenant.id, tenant.name ...


will remove duplicate rows, but my question is:

Is it possible to avoid using DISTINCT and construct JOINs in such a way that no duplicate rows will be returned? Already tried all combinations of INNER, LEFT JOINS without much luck :(

Any suggestions very much appreciated

3 Answers
3

Jonh Smith is a tenant for two properties from Best Homes Ltd so he comes out twice, so you have to mess about with group by or distinct etc.

To get what you want without that you'd need another table say

Portfolio
Which would give you one record linking tentant to kandlord.
Then you could use that to link portfolio to property. ie you group up multiple tenancies, so you don't have to use the tennat_has_property table to see the relation.

Whether it's worth the restructure of the schema I've no idea.

True version is

SELECT id, name FROM tenant
WHERE id IN
(SELECT tenant_has_property.tenant_id FROM
tenant_has_property
WHERE tenant_has_property.property_id IN
(SELECT property.id FROM property WHERE property.landlord_id = 1 )
)


I think that should work for you. But i didnt try it cause i dont have access to a sql server on my terminal.

You can do it with the following query using an IN clause:

IN

SELECT
t.id, t.name
FROM
tenant t
WHERE
t.id IN (
SELECT
thp.tenant_id
FROM
tenant_has_property thp
INNER JOIN property p ON thp.property_id = p.id
WHERE
p.landlord_id = 1
)


But I strongly suggest you not to use this method because inner SQL statements tend to run slower than JOIN queries. You can use GROUP BY to group the results by tenant.id.

GROUP BY

SELECT
t.id, t.name
FROM
tenant t
INNER JOIN tenant_has_property thp ON t.id=thp.tenant_id
INNER JOIN property p ON thp.property_id = p.id
WHERE
p.landlord_id = 1
GROUP BY t.id


Achieving this with only JOIN statements is not possible as far as I know. Because MySQL will join the records as you requested and then will end up with following rows:

Since you request only t.id and t.name fields, MySQL returns only that columns to you but it does not know that they have duplicate values. You either have to force it by adding DISTINCT to your select clause or telling MySQL how to group the records and return a single row.

DISTINCT

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.