|
|
Posted by Erland Sommarskog on 10/02/91 11:44
Igor (jerosimic@gmail.com) writes:
> I have one SELECT statement that needs to return one ntext field from
> one table and count something from other table, problem is that all
> fileds that are not in count have to be in group by and ntext can't be
> in group by... i hope you understend what i want to say here :), so is
> there any solution to this problem or what is the best workaraund you
> would use?
>
> example:
> TABLE projects
> project_id int
> project_title nvarchar(50)
> project_description ntext
>
> TABLE users
> user_id int
> project_id int
>
> SELECT projects.project_id, projects.project_title,
> projects.project_description, count(users.*) AS number_od_users
> FROM projects LEFT JOIN users ON projects.project_id=users.project_id
> GROUP BY projects.project_id, projects.project_title,
> projects.project_description;
>
> this select is something that would work for me...if it wasn't for that
> tiny problem with ntext field
SELECT p.project_id, p.project_title, p.project_description,
numbers_od_users = coalesce(u.cnt, 0)
FROM projects p
LEFT JOIN (SELECT project_id, cnt = COUNT(*)
FROM users
GROUP BY project_id) AS u ON p.project_id = u.project_id
The thing in parens is a derived table. Logically it is a temp table within
the table, but it never materialised, and the actual computation order may
be different. It is a very powerful tool to write SQL queries.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|