SQL Server - using a column alias in a subquery
I have the following query that works fine with MySQL, but refuses to work with SQL Server:
SELECT table1.someField AS theField,
COUNT(table2.someField) / (SELECT COUNT(someField) FROM table1 WHERE someField = theField),
FROM table1 LEFT JOIN table2 ON table1.someField = table2.someField
SQL Server doesn't seem to be like an alias in a subquery. I was told that I need to use CTEs, but I have never used them before. Is it correct?
0
source to share
1 answer
The problem might be confusion in the subquery
SELECT COUNT(someField) FROM table1 WHERE someField = theField
the someField
in the condition will be local, but you can get table1.someField
the same thing.
What about
SELECT COUNT(t3.someField) FROM table1 t3 WHERE t3.someField = table1.someField
?
+3
source to share