table alias mssql problem

[Bugsville]

I get this error:
The column prefix 'F' does not match with a table name or alias name used in
the query.

with the query below

Any ideas as to the problem?

TIA!
- b

<cfquery name="getFolders" datasource="#request.datasource#">
select F.*
from Folders F, folderPerm P
INNER JOIN Folders S ON S.foldermaster = F.FolderID
where F.folderid = P.FolderID
and P.staffID = #arguments.memberID#
and (F.folderMaster = 0)
order by #arguments.sortby#
</cfquery>

[philh]

Hi Bugs,

Does this query work in Query Analyzer with hard-coded information?

[Bugsville]

It does not, I get the same error.

It all looks proper so I am at my wits end as to what the problem could be.

[LL@Work]

Take a closer look at your syntax.

"INNER JOIN Folders S" should be "INNER JOIN Folders F"

[Bugsville]

Well, that didn't seem right to me, but I tried it anyway and got:
"The correlation name 'F' is specified multiple times in a FROM clause."

any other ideas? It all looks correct to me...

[paross1]

Well, you have two tables, Folders F and folderPerm P, in your FROM clause,
then an INNER JOIN with a third. All three should be joined with INNER JOIN
statements. Something like the example below:

select F.*
from Folders F
INNER JOIN folderPerm P ON F.folderid = P.FolderID
INNER JOIN Folders S ON S.foldermaster = F.FolderID
where P.staffID = #arguments.memberID#
and (F.folderMaster = 0)
order by #arguments.sortby#

Phil

[Bugsville]

That resolved the error, but It is not delivering the results I am expecting.
Maybe this will help:
What I have is a folders table and a folderPerm (folderpermissions) table
I want to pull the folders that the specific user has permission set to view.
Now in the folders table, there is a masterfolder field, which is the ID of
the folder that the specific record is a SUBfolder of.
So the query needs to deliver all folders and subfolders that the user has
permission to view and some folders do NOT have subfolders.

does that make sense?

And thanks to all who have helped so far!

[paross1]

Are you missing the ones that have no subfolders? Try a LEFT (OUTER) join.

select F.*
from Folders F
INNER JOIN folderPerm P ON F.folderid = P.FolderID
LEFT JOIN Folders S ON S.foldermaster = F.FolderID
where P.staffID = #arguments.memberID#
and (F.folderMaster = 0)
order by #arguments.sortby#

Phil

[paross1]

.....or is this more like what you are trying to do?

SELECT F.*
FROM Folders F
INNER JOIN folderPerm P ON F.folderid = P.FolderID
WHERE P.staffID = #arguments.memberID#
AND (F.folderMaster = 0)
UNION
SELECT S.*
FROM Folders S
INNER JOIN Folders F ON S.foldermaster = F.FolderID
INNER JOIN folderPerm P ON F.folderid = P.FolderID
WHERE P.staffID = #arguments.memberID#
AND (F.folderMaster = 0)
ORDER BY #arguments.sortby#

Phil

[Bugsville]

Originally posted by: paross1
Are you missing the ones that have no subfolders? Try a LEFT (OUTER) join.

Exactly what I needed! thanks!