hlh3406 hlh3406 - 1 month ago 17
SQL Question

INNER JOIN Count Unknown column in ON clause

I'm using HeidiSQl and trying to run a query where I get all the users from table 1 and then count all the matching users in table 2.

SELECT DISTINCT problem, title, username, createdate, expiredate
FROM solutionhistory as table1
INNER JOIN (SELECT COUNT(LOGIN_NAME) FROM ra_report_table) as table2
ON table1.username=table2.LOGIN_NAME
WHERE username != 'test'
LIMIT 10


I'm a bit stuck as to where I'm going wrong! I'm getting the error unknown column in on clause for
table2.LOGIN_NAME
.

The query works if I take the reference to
COUNT(LOGIN_NAME)
out - if someone could point me in the right direction that would be great!

Answer

Your query doesn't make sense on a lot of levels. I am guessing you intend something more like this:

SELECT problem, title, username, createdate, expiredate, table2.cnt
FROM solutionhistory as table1 INNER JOIN
     (SELECT LOGIN_NAME, COUNT(LOGIN_NAME) as cnt
      FROM ra_report_table
      GROUP BY LOGIN_NAME
    ) as table2
    ON table1.username = table2.LOGIN_NAME
WHERE username <> 'test'
LIMIT 10