Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Dec 2002
    Location
    NW Iowa
    Posts
    185
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Simple SQL Query problem (ORDER BY)

    Here is current query (very long):
    Code:
    "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:
    Code:
    "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.

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    if you put some formatting to the query, you'll see that you have missing ))'s:

    Code:
    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
    I am the luckiest man in the world

  • #3
    Regular Coder
    Join Date
    Dec 2002
    Location
    NW Iowa
    Posts
    185
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Here is the error I get now:
    Code:
    [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

  • #4
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You're missing one '(' in your JOIN

    Bostjan


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •