CODEWITHSUNDEEP
mysqlsqlinner-joinmysql-workbench
python
python

Reputation: 4521

Issues with inner join in mysql

I want to join output of one query with the second table in mysql:

(select 
    A.name, A.address, sum(C.penalty_points) as points
from
    restaurant as A
        inner join
    inspection as B ON A.restaurant_id = B.restaurant_id
        inner join
    violation as C ON C.violation_id = B.violation_id
group by A.name )

Output:

name               address                                    points
Kitchen 305        660 Washington Ave                         2
PL8 Kitchen        Southeast 17th Street in Fort Lauderdale   11
Prime One Twelve   112 Ocean Drive                            5
Seasons 52         Palm Beach Gardens                         3
Six Tables         32 East Atlantic                           8
Table 26           Park Racks Downtown Eatery                 2

The result of the second table: 

select * from health_points

Output:

points   health_grade
0        A
1        A
2        A
3        A
4        A
5        B
6        B
7        B
8        B
9        B
10       B
11       C
12       C
13       C
14       C
15       C
17       FAIL
18       FAIL
19       FAIL

Is there a way I can combine the first query with the second table and extract the health grade ? I was trying something like:

(select 
    A.name, A.address, sum(C.penalty_points) as points
from
    restaurant as A
        inner join
    inspection as B ON A.restaurant_id = B.restaurant_id
        inner join
    violation as C ON C.violation_id = B.violation_id
group by A.name ) as D inner join health_points as E  on D.points = E.points

But it is showing error in mysql? Any pointers where I am going wrong here?

Upvotes: 0

Views: 53

Answers (2)

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

You are missing the outer SELECT clause:

SELECT D.*, E.health_grade
FROM (
  SELECT A.name, A.address, sum(C.penalty_points) as points
  FROM restaurant A
  JOIN inspection B ON (A.restaurant_id = B.restaurant_id)
  JOIN violation C ON (C.violation_id = B.violation_id)
  GROUP BY A.name
) D
JOIN health_points E ON (D.points = E.points)

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can do this:

SELECT
  e.health_grade,
  d.points
FROM
(
  select 
    A.name, A.address, sum(C.penalty_points) as points
  from restaurant as A
  inner join inspection as B ON A.restaurant_id = B.restaurant_id
  inner join violation as C ON C.violation_id = B.violation_id
  group by A.name, A.address
) as D 
inner join health_points as E  on D.points = E.points

Upvotes: 2

Related Questions