MySQL throws syntax error near `INNER JOIN SQL`

Question

I have to get the name and hacker_id from of hackers with a full score on at least 2 or more challenges. This is my code and the error code that I keep getting, but I can't figure out what to do next to correct it.

SELECT DISTINCT hacker_id, name
FROM hackers
WHERE hacker_id in
 (SELECT s.score, hacker_id
  FROM submissions as s
  INNER JOIN 
  difficulty as d
  on s.score=d.score
  GROUP BY hacker_id
  HAVING count(hacker_id)>1) as x
INNER Join hackers as h
ON x.hacker_id=h.hacker_id
ORDER BY COUNT(challenge_id) DESC, hacker_id ASC;

ERROR 1064 (42000) at line 21: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as x inner Join hackers as h on x.hacker_id=h.hacker_id order by count(challenge' at line 10

You can also just join with the subquery instead of using WHERE hacker_id IN — Barmar
– Barmar, Commented Feb 24, 2022 at 17:45
Your query has several other issues too. 1) The GROUP BY is invalid. 2) The IN wants a one column result set. 3) The count() is not available in the ORDER BY,. — jarlh
– jarlh, Commented Feb 24, 2022 at 17:45
You can't use ON x.hacker_id because the x subquery isn't a table you joined with. — Barmar
– Barmar, Commented Feb 24, 2022 at 17:46
It looks like you're trying to use both JOIN and WHERE IN for the same thing. Pick one or the other. — Barmar
– Barmar, Commented Feb 24, 2022 at 17:46

Barmar · Accepted Answer · 2022-02-24 17:57:54Z

3

Just use a simple join with submissions, with grouping in the main query.

Use COUNT(DISTINCT s.challenge_id) to count the number of challenges they submitted for.

SELECT h.hacker_id, h.name
FROM hackers AS h
JOIN submissions AS s ON s.hacker_id = h.hacker_id
JOIN difficulty AS d ON s.score = d.score
GROUP BY h.hacker_id
HAVING COUNT(DISTINCT s.challenge_id) > 1
ORDER BY COUNT(DISTINCT s.challenge_id) DESC, hacker_id ASC

You don't need SELECT DISTINCT because GROUP BY h.hacker_id ensures that the rows will be distinct.

answered Feb 24, 2022 at 17:57

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

2 Comments

jarlh Over a year ago

Does MySQL also want h.name in the GROUP BY clause if ONLY_FULL_GROUP_BY is enabled?

Barmar Over a year ago

It's not needed if hacker_id is the primary key of the table.

Collectives™ on Stack Overflow

MySQL throws syntax error near `INNER JOIN SQL`

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related