How to write a JOIN Query with a Where clause

Open Q & A Querying your data
1

:question:QUESTION
I have this query where I’m getting an error:

SELECT
    Dev.FavoriteToppings.veggies as veggies,
    SUM (Dev.FavoriteToppings.fruitsNum) as fruitsNum,
    Dev.UserAccounts.foodAvatar as foodAvatar,
    Dev.UserAccounts.displayName as displayName
FROM
    Dev.FavoriteToppings
WHERE
    communityId = 'abc123'
    INNER JOIN Dev.UserAccounts ON Dev.FavoriteToppings.veggies = Dev.UserAccounts.foodAccountId
GROUP BY
    veggies,
    communityId
ORDER BY
   fruitsNum DESC

Error [Query]
Incorrect SQL syntax near 'INNER'. Please inspect your query for correct syntax.. Position: line: 9, ch: 1
Live SQL Support

How can I fix it?

:white_check_mark:ANSWER
The query is slightly written wrong: you first want to do the INNER JOIN, then you want to use the WHERE clause. The other thing you can do to make it easier to read is you can alias your collection with AS. So, something like this will work:

SELECT
    ft.veggies as veggies,
    SUM (ft.fruitsNum) as fruitsNum,
    ua.foodAvatar as foodAvatar,
    ua.displayName as displayName
FROM
    Dev.FavoriteToppings as ft
    INNER JOIN Dev.UserAccounts as ua ON ft.veggies = ua.foodAccountId
WHERE
    ft.communityId = 'abc123'
GROUP BY
    ft.veggies,
    ft.communityId,
    ua.displayName,
    ua.foodAvatar
ORDER BY
  fruitsNum DESC;