This is your query:
SELECT
FirstName,
LastName,
(
SELECT COUNT(O.Id)
FROM [Order] O
INNER JOIN C On O.CustomerId = C.Id
) AS OrderCount
FROM Customer C;
It is invalid, because in the sub query you are selecting from C.
This is a bit complicated to explain. In a query, we deal with tables and table rows. E.g.:
select person.name from person;
FROM person means “from the table person“. person.name means “a person’s name“, so it is referring to a row. It would be great if we could write:
select person.name from persons;
but SQL doesn’t know about singular and plural in your language, so this is not possible.
In your query FROM Customer C means “from the customer table, which I’m going to call C for short”. But in the rest of the query including the sub query it is one customer row the C refers to. So you cannot say INNER JOIN C, because you can only join to a table, not a table row.
One might try to make this clear by using plural names for tables and singular names as table aliases. If you’d make it a habit, you’d have FROM Customers Customer in your main query and INNER JOIN Customer in your inner query, and you’d notice from your habits, that you cannot have a singular in the FROM clause. But well, one gets quickly accustomed to that double meaning (row and table) of a table name in a query, so this would just be kind of over-defensive, and we’ll rather use alias names to get queries shorter and more readable, just as you are doing it with abbreviating customer to c.
But yes, you can use joins instead of sub queries in the SELECT clause. Either move the sub query to the FROM clause:
SELECT
c.firstname,
c.lastname,
COALESCE(o.ordercount, 0) AS ordercount
FROM customer c
LEFT JOIN
(
SELECT customerid, COUNT(*) AS ordercount
FROM [order]
GROUP BY customerid
) o ON o.customerid = c.id;
Or join without a sub query:
SELECT
c.firstname,
c.lastname,
COUNT(o.customerid) AS ordercount
FROM customer c
LEFT JOIN [order] o ON o.customerid = c.id
GROUP BY c.firstname, c.lastname;
0
solved Joins and Subqueries