Well, I'll be! It worked. This is the second time that this has
happened to me where the absence of an alias has screwed things up.
Thanks Joe!
George
On 7/8/05, Joe Rinehart <[EMAIL PROTECTED]> wrote:
> Hey George,
>
> AFAIK, derived tables in T-SQL need an alias...try this:
>
> SELECT count(
>>I think it's light years better than the union query
There is still a union, and if the subqueries generate a long list, it
must be pretty unefficient.
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please
Hey George,
AFAIK, derived tables in T-SQL need an alias...try this:
SELECT count(*)
FROM (
SELECT ObjectID
FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID
UNION
SELECT ObjectID
FROM table1 t1 INNER JOIN table3 t3 ON t1.thisID = t3.thisID
)
>>I just solved it using an ugly workaround.
Indeed, unless you really need the count to occur in the query, it
should be more efficient to make
two separate queries and just add their record counts in CF.
--
___
REUSE CODE! Use custom tags;
See http://www.c
There you go... it's not ugly - I think it's light years better than the
union query :)
-Original Message-
From: George Abraham [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 07, 2005 4:03 PM
To: CF-Talk
Subject: Re: OT: SQL count of rows from a union query
Ah,
That explains
July 07, 2005 3:59 PM
To: CF-Talk
Subject: Re: SQL count of rows from a union query
Mark,
I am trying to return multiple rows of similarly typed data,
specifically one column named ObjectID. Only, instead of deriving the
number of rows from a getResults.RecordCount, I want to do it in the
SQL que
Ah,
That explains it. I just solved it using an ugly workaround.
SELECT count(*) FROM table1
WHERE ObjectID IN(
SELECT ObjectID
FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID
UNION
SELECT ObjectID
FROM table1 t1 INNER JOIN table3 t3 ON t1.thisID = t3.th
CSE
> > www.cfwebtools.com
> > www.necfug.com
> > http://mkruger.cfwebtools.com
> >
> >
> >
> >
> > -Original Message-
> > From: George Abraham [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, July 07, 2005 2:22 PM
> > To: CF-Talk
>
> -Original Message-
> From: George Abraham [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 07, 2005 2:22 PM
> To: CF-Talk
> Subject: OT: SQL count of rows from a union query
>
>
> Hi all,
> This one seems to be simple, but it is not working at all
>>
SELECT count(*)
FROM (
SELECT ObjectID
FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID
UNION
SELECT ObjectID
FROM table1 t1 INNER JOIN table3 t3 ON t1.thisID = t3.thisID
)
AFAIK, one can have a subquery in a WHERE or HAVING clause, but not insid
MCSE
> www.cfwebtools.com
> www.necfug.com
> http://mkruger.cfwebtools.com
>
>
>
>
> -Original Message-
> From: George Abraham [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 07, 2005 2:22 PM
> To: CF-Talk
> Subject: OT: SQL count of rows from a union q
Sent: Thursday, July 07, 2005 2:22 PM
To: CF-Talk
Subject: OT: SQL count of rows from a union query
Hi all,
This one seems to be simple, but it is not working at all. I want to
return a count of the rows from an T-SQL Query that involves a UNION
operation.
SELECT count(*)
FROM (
Hi all,
This one seems to be simple, but it is not working at all. I want to
return a count of the rows from an T-SQL Query that involves a UNION
operation.
SELECT count(*)
FROM (
SELECT ObjectID
FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID
UNION
SELECT
13 matches
Mail list logo