CODEWITHSUNDEEP
mysqlsql
user
user

Reputation: 914

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)

);

Upvotes: 1

Views: 1030

Answers (5)

Laxmikant
Laxmikant

Reputation: 588

you can simply take the result of inner query by combining joins and compare with outer query

Select has_property.userId
From has_property 
Where has_property.propertyId IN
(
    select hRR.propertyId as id 
    From hasRatings_Rate hRR join house h on h.id = hrr.uid and hrr.ratingid = 5
    join has_weather hw on hw.homeaddid = h.homeaddid
    join weather_year wy on hw.weatherid = wy.weatherid AND wy.AvgTemp > 55
)

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You can try like this:

Select has_property.userId
From has_property 
Where has_property.propertyId IN
(
   Select hRR.propertyId as id
   From hasRatings_Rate hRR INNER JOIN House h on hRR.propertyId = h.propertyId
      INNER JOIN has_weather hw on hw.homeAddId = h.homeAddId
      INNER JOIN weather_year wy on hw.weatherId = wy.weatherId
   Where hRR.ratingId = 5 and wy.AvgTemp > 55
)

Also try to avoid comma seperated JOINs in future.

Upvotes: 1

Barmar
Barmar

Reputation: 780994

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.

Upvotes: 3

Tapas Pal
Tapas Pal

Reputation: 7207

It should be

 Select has_property.userId
    From has_property 
    Where has_property.propertyId IN
    (
        SELECT a.id FROM //This line need to add
        (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)

);

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

Try this:

Select has_property.userId
From has_property 
INNER JOIN
(
    SELECT id 
    FROM
    (
    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)
) c ON(has_property.propertyId = c.id)

Upvotes: 1

Related Questions