We're running MySQL 4.1.22.
Two websites are powered by a single mySQL database. One website provides restaurant reviews, and the other provides golf course reviews. The database has two tables: "Properties" and "Reviews". The Properties table has a field called "Type", which for each record is either "restaurant" or "golf course".
The code should generate a single list of restaurants, ordered into three groups:
1. First, restaurants reviewed and starred
2. Second, restaurants reviewed but not starred
3. Third, restaurants neither reviewed nor starred
But so far I'm only able to do this for all properties, golf courses and restaurants alike. Here's the raw code so far:
$query="SELECT p.id
, p.star
, p.property
FROM properties AS p
LEFT OUTER
JOIN ( SELECT DISTINCT link
FROM reviews ) AS r
ON r.link = p.id
ORDER
BY p.star DESC
, CASE WHEN r.link IS NULL
THEN 'last'
ELSE 'first' END
, p.property";
