Skip to content
Advertisement

MySQL transform 3 queries into one

I have 3 working simple queries. How could I transform in one query?

// query 1
SELECT PropertyName From Properties Where PropertyID = 1

// query 2
Select Count(*) as totalTenants From Tenants Where Prospect = 2 and PropertyID = 1

// query 3
Select Count(*) as totalUnits From units Where PropertyID = 1

Thanks

Advertisement

Answer

I am guessing you want:

select p.PropertyName,
       (select count(*) as totalTenants 
        from Tenants t
        where t.Prospect = 2 and t.PropertyID = p.PropertyID
       ),
       (select count(*) as totalTenants 
        from Units u
        where u.PropertyID = p.PropertyID
       )
from Properties p
where p.PropertyID = 1;
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement