multiple inner joins

[Anderson11983]

Hi,

im a newbi to coldfusion and i need to multiple inner joins between tables in
a database - some one has give me the sql version(from an old stored procedure)
and i need to convert it to coldfusion syntax etc.

I've had a go and now im stuck and i was hoping someone can help me and point
in the right direction - if included what ive done :(

Appreciate any and all help

Thanks
Andy



<cfquery name="AttachedToFlashCount" datasource="#caller.nimoirdb#">

SELECT count(LnC.ID) FROM links LnC
INNER JOIN pageHasFlash phF on LnC.id = phF.linkID
INNER JOIN itemPage pC on phF.pageID = pC.id
INNER JOIN tracking tC on pC.trackingID = tC.id

WHERE (LnC.linkType = "download" AND LnC.linkTo = <cfqueryparam
name="#objectID#" cfsqltype="CF_SQL_INT"> AND tC.status NOT IN "removed",
"purged")
</cfquery>

[paross1]

The only thing that I see that is obviously wrong is this:

LnC.linkType = "download"
and
tC.status NOT IN "removed", "purged"

try

LnC.linkType = 'download'
and
tC.status NOT IN ('removed', 'purged')

instead

Phil

[ctrl+alt+delete]

If you have a stored procedure that works, stick with that and use the
<cfstoredproc> tag.

"Anderson11983" <webforumsuser@macromedia.com> wrote in message
news:d583ru$rv0$1@forums.macromedia.com...

> Hi,
>
> im a newbi to coldfusion and i need to multiple inner joins between tables
> in
> a database - some one has give me the sql version(from an old stored
> procedure)
> and i need to convert it to coldfusion syntax etc.
>
> I've had a go and now im stuck and i was hoping someone can help me and
> point
> in the right direction - if included what ive done :(
>
> Appreciate any and all help
>
> Thanks
> Andy
>
>
>
> <cfquery name="AttachedToFlashCount" datasource="#caller.nimoirdb#">
>
> SELECT count(LnC.ID) FROM links LnC
> INNER JOIN pageHasFlash phF on LnC.id = phF.linkID
> INNER JOIN itemPage pC on phF.pageID = pC.id
> INNER JOIN tracking tC on pC.trackingID = tC.id
>
> WHERE (LnC.linkType = "download" AND LnC.linkTo = <cfqueryparam
> name="#objectID#" cfsqltype="CF_SQL_INT"> AND tC.status NOT IN "removed",
> "purged")
> </cfquery>
>
>

[Anderson11983]

Thanks for your help guys,

Besides make the alterations that paross1 suggested does everything else look
fine?
I can't put them in a stored procedure tag as ive got to convert all nof our
ctored procedures to .cfc.

Thanks Again for your help

Andy