SQL inner join syntax error

I have this error. I am new to SQL and can’t figure what is wrong with my syntax. I changed my INTERSECT statement to inner join realizing SQL does not accept such syntax. However I continue to get an error.

ERROR:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n
ear 'a
INNER JOIN
(Select h.propertyId as id
From House h, has_weather hw, weather_ye' at line 8

The query is suppose to find the userIDs and the email of user who’s property has ratings equal to 5 and has AvgTemp over 55.

    Select has_property.userId
    From has_property 
    Where has_property.propertyId IN
    (
        (Select hRR.propertyId as id
        From hasRatings_Rate hRR
        Where hRR.ratingId = 5
        ) a

        INNER JOIN

        (Select h.propertyId as id
        From House h, has_weather hw, weather_year wy
        Where hw.weatherId = wy.weatherId AND hw.homeAddId = h.homeAddId AND wy.AvgTemp > 55
        )b
        ON (a.id = b.id)

);

Answer

Try this:

Select has_property.userId
From has_property hp
JOIN hasRatings_Rate hRR ON hp.propertyId = hRR.propertyId
JOIN House h ON h.id = hRR.id
JOIN has_weather hw ON hw.homeAddId = h.homeAddId
JOIN weather_year wy ON hw.weatherId = wy.weatherId
WHERE hRR.ratingId = 5
AND  wy.avgTemp > 55

No need for any subqueries, just a succession of joins.

Advertisement

Answer