SQL Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wally Kolcz - 29 Aug 2006 01:52 GMT

I need help with a SQL statement. What I would like to do in a single query
is count the records in one table and grab a number from another. I
attempted to write this:

SELECT Count(p.project_id) AS Total, m.projects
FROM project P, members M
WHERE p.dirname = m.username
AND m.dirname = '#arguments.username#'

I now realize I cannot have COUNT and a single varable in the same SQL
statement without a group by statement and I am lost. I want to count all
the project id's from one table and grab the column value from another
called 'projects' in the 'members' table.

I only want 2 items returned from the SQL statement: a number (Total) and a
the number from the project column of members.

Please help me.

Signature

Wally Kolcz
Developer / Support

Reply to this Message

Webmaster - 29 Aug 2006 02:38 GMT

You need to do a SubQuery,, I am assuming you are using SQL Server

I am a little unclear on your post, but you need to do something like
this. You should be able to figure it out.

SELECT Count(project_id) AS Total
(SELECT username
From members
WHERE username = A.dirname) AS username

>From Projects A

Where dirname = '#arguments.username#'

Allen

> I need help with a SQL statement. What I would like to do in a single query
> is count the records in one table and grab a number from another. I
[quoted text clipped - 14 lines]
>
> Please help me.

Reply to this Message

Dan Bracuk - 29 Aug 2006 04:27 GMT

select field1, field2, etc, (select count(*) from some_other_table) abc
from etc

Reply to this Message

<newbie /> - 30 Aug 2006 16:33 GMT

> SQL statement without a group by statement

Any reason why you don't want to use a GROUP BY statement?

I'm not sure of the syntax but something like this...

SELECT Count(p.project_id) AS Total
, m.projects
FROM project P, members M
WHERE p.dirname = m.dirname
AND m.username = <cfqueryparam value="#arguments.username#"
cfsqltype="CF_SQL_VARCHAR" />
GROUP BY m.projects

Good luck!

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / August 2006

SQL Help