The multi-part identifier could not be bound in SQL Server and it confused

Question

I have this SQL query:

SELECT 
    stu.sno, sname, cname
FROM
    sc scc,
    (SELECT AVG(sc.grade) AS avg_grade 
     FROM sc 
     GROUP BY sc.cno) AS avg_grades
INNER JOIN 
    course c ON c.cno = scc.cno
INNER JOIN 
    s stu ON stu.sno = scc.sno;

And there is an error that the multi-part identifier scc.cno could not be bound. I'm confused - could someone help me?

Answer 1

Don't mix implicit and explicit joins! Matter of fact, don't use implicit joins: this is archaic syntax, that should not appear in new code.

The comma in the FROM clause should (probably) be a CROSS JOIN:

SELECT stu.sno, sname, cname
FROM sc scc
CROSS JOIN (SELECT AVG(sc.grade) AS avg_grade FROM sc GROUP BY sc.cno) AS avg_grades
INNER JOIN course c on c.cno = scc.cno
INNER JOIN s stu on stu.sno = scc.sno;

Note that, for this subquery to be useful, you would probably need to select column avg_grade. I would also recommend prefixing each column with the table it belongs to, to remove any possible ambiguity.

Finally: you (probably) can use window functions instead of a subquery:

SELECT stu.sno, sname, cname, scc.
FROM (SELECT *, AVG(grade) OVER() avg_grade FROM sc) scc
INNER JOIN course c on c.cno = scc.cno
INNER JOIN s stu on stu.sno = scc.sno;