Subquery with inner join

Question

SELECT c.first_name, c.last_name
FROM customer c
INNER JOIN payment p ON C.CUSTOMER_ID = p.customer_id
WHERE p.amount > (SELECT AVG(amount) FROM payment)
INNER JOIN city ON city_id = c.city.city_id
WHERE c.city LIKE '%a%'

ERROR:  syntax error at or near "INNER"
LINE 6: INNER JOIN city
        ^
SQL state: 42601
Character: 155

A couple of doubts here, I need some more data. On the 4th line `ON C.CUSTOMER_ID = p.customer_id` why `C` and not `c`? I expect a small `c` and not a capital one. You are accessing `ON city_id = c.city.city_id` on line 7. How come this is possible `c.city LIKE '%a%'` shows that `c.city` is strings then `c.city.city_id` is not accessible. — Himanshu Patel, Dec 14 '22 at 17:46
Just a mistake on my part having caps on. It should be lower case but it will run regardless if syntax is correct — Mason, Dec 15 '22 at 02:36
Does this answer your question? [Order Of Execution of the SQL query](https://stackoverflow.com/questions/4596467/order-of-execution-of-the-sql-query) — philipxy, Jan 07 '23 at 18:47
[mre] [ask] [Help] [research effort](https://meta.stackoverflow.com/q/261592/3404097) [“help me"](https://meta.stackoverflow.com/q/284236/3404097) — philipxy, Jan 07 '23 at 18:48

score 1 · Answer 1 · answered Dec 14 '22 at 08:50

Second "INNER JOIN" must be before "WHERE"

SELECT 
    c.first_name, 
    c.last_name
FROM customer c
INNER JOIN payment p ON (p.customer_id = c.CUSTOMER_ID)
INNER JOIN city ON (city.city_id = c.city_id)
WHERE 
    p.amount > (SELECT AVG(amount) FROM payment)
    AND city.city LIKE '%a%'

Subquery with inner join

1 Answers1