Ah feature creep, how I love you. The user has now asked for the report to be sorted by streetname. It makes it easier for the checkvalve tester to do their job by testing an entire street the same day. So I went back and tried to add that to the query. It would have worked except for the fact that the address table contains a lookup to the streets table. With the following query I am prompted to enter addnum and streetid. If I press enter both times the query runs, but doesn't sort.
Code:
SELECT checkvalves.*, (SELECT address.addressnumber FROM address WHERE checkvalves.address = address.id) AS addnum, (SELECT address.streets_id FROM address WHERE checkvalves.address = address.id) AS streetid
FROM checkvalves INNER JOIN (SELECT checkvalves.address, max(checkvalves.PassedDate) AS Latest
FROM checkvalves
GROUP BY checkvalves.address) AS LP ON (checkvalves.PassedDate=LP.Latest) AND (checkvalves.Address=LP.Address)
ORDER BY checkvalves.PassedDate, streetid, addnum;
I tried a left join but I get a syntax error:
Code:
SELECT checkvalves.*, address.*, streets.*
FROM checkvalves INNER JOIN (SELECT checkvalves.address, max(checkvalves.PassedDate) AS Latest
FROM checkvalves
GROUP BY checkvalves.address) AS LP ON (checkvalves.PassedDate=LP.Latest) AND (checkvalves.Address=LP.Address)
LEFT JOIN streets ON (checkvalves.address = address.id AND address.streets_id = streets.id)
ORDER BY checkvalves.PassedDate, streets.streetname, address.addressnumber
Address Table
Code:
ID AddressNumber Street_ID (lookup to streets)
1 10001 64
2 10002 128
3 9300 34
Streets table
Code:
ID StreetName
1 Main St
2 2nd St
3 Joliet St