Filtering a mySQL select query

[kjm7267]

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";

 

 

 

[fenway]

Looks like a double-post to me.