SQL inner join syntax error

Question

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)

);

Barmar · Accepted Answer · 2015-11-10 06:39:20Z

3

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.

answered Nov 10, 2015 at 6:39

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohar Peled · Accepted Answer · 2015-11-10 06:37:12Z

1

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)

answered Nov 10, 2015 at 6:37

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Comments

Tapas Pal · Accepted Answer · 2015-11-10 06:37:53Z

1

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)

);

answered Nov 10, 2015 at 6:37

Tapas Pal

7,2578 gold badges45 silver badges90 bronze badges

Comments

Rahul Tripathi · Accepted Answer · 2015-11-10 06:39:40Z

1

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.

answered Nov 10, 2015 at 6:39

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Comments

Martijn Pieters · Accepted Answer · 2022-09-17 17:49:28Z

1

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
)

edited Sep 17, 2022 at 17:49

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Nov 10, 2015 at 7:04

Laxmikant

5984 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL inner join syntax error

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related