Simple SQL Query problem (ORDER BY)

[mattboy_slim]

Here is current query (very long):

"SELECT DISTINCTROW categories.categoryname, people.peopleID, people.firstname, people.lastname, properties.* FROM (properties INNER JOIN (categories INNER JOIN category_property_link ON categories.categoryID = category_property_link.categoryID) ON properties.propertyID = category_property_link.propertyID) INNER JOIN (people INNER JOIN people_property_link ON people.peopleID = people_property_link.peopleID) ON properties.propertyID = people_property_link.propertyID WHERE categories.supercategory <> 0"


When I try to add an ORDER BY clause, it breaks:

"SELECT DISTINCTROW categories.categoryname, people.peopleID, people.firstname, people.lastname, properties.* FROM (properties INNER JOIN (categories INNER JOIN category_property_link ON categories.categoryID = category_property_link.categoryID) ON properties.propertyID = category_property_link.propertyID) INNER JOIN (people INNER JOIN people_property_link ON people.peopleID = people_property_link.peopleID) ON properties.propertyID = people_property_link.propertyID WHERE categories.supercategory <> 0 ORDER BY properties.listing_price DESC"


Can anybody assist me, I'm flustered already at 9:00 AM.

[Roelf]

if you put some formatting to the query, you'll see that you have missing ))'s:

SELECT DISTINCTROW
categories.categoryname,
people.peopleID,
people.firstname,
people.lastname,
properties.*
FROM (properties
INNER JOIN (categories
INNER JOIN category_property_link
ON categories.categoryID = category_property_link.categoryID)
ON properties.propertyID = category_property_link.propertyID)
INNER JOIN (people
INNER JOIN people_property_link
ON people.peopleID = people_property_link.peopleID)
ON properties.propertyID = people_property_link.propertyID)
WHERE categories.supercategory <> 0
ORDER BY properties.listing_price DESC

I think the blue one can be deleted (after category_property_link.propertyID), and the red one (after people_property_link.propertyID) must be added

[mattboy_slim]

Here is the error I get now:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'properties.propertyID = category_property_link.propertyID INNER JOIN (people INNER JOIN people_property_link ON people.peopleID = people_property_link.peopleID) ON properties.propertyID = people_property_link.propertyID'.


Thanks,
Matt

[bostjank]

You're missing one '(' in your JOIN

Bostjan