JustinBKay.org

The other day I was creating a Crystal Report that was accessing a MySQL database. I created the report and everything was looking good and then I got that email. The one that says: that report is awesome... but you know what it needs...

The report was centered around the amount of time that it took to make a unit at a property rent ready after it was vacated. The information that was requested was the number of units at the complex. I don't have that number anywhere in my database, so I knew that I'd need to calculate it by doing a sql count. The problem was trying to get all of this information returned in a single record set for Crystal. So I thought to myself. Wouldn't it be great if you could join to a sub-select that was the count? And guess what, you can. So here is what it looks like:

SELECT propname,
numunits,
unitnum,
dtvac,
'' status,
rrdate,
if(rrdate = '0000-00-00',datediff(curdate(),dtvac),datediff(rrdate,dtvac)) datediff
FROM unit_history u inner join property p on u.propid=p.propid
inner join (select p.propid, count(*) numunits from unit u inner join property p on u.propid=p.propid group by p.propid) c on p.propid=c.propid
where dtvac between '2007-01-01' and '2007-07-15' and propport = 3 and rrdate != '0000-00-00'

It works like a charm and saves me from having to try to use multiple queries from Crystal Reports