You can't combine the ANSI syntax and the WHERE clause syntax for a join
like that. Also, if you're wanting to use a subquery, you have to start
the subquery with a SELECT statement. If you then want to use the subquery
in a join, you must assign the subquery a table alias. The queries below
are nowhere near what you're wanting, but they show the correct syntax for
a joined subquery, first using the older, WHERE clause syntax :
>FROM
> (SELECT WebUser.*,
> Email.*,
> [Level].*
FROM WebUser
> WHERE
> WebUser.PartNum IS NOT NULL
> AND
> WebUser.fkEmail = Email.pkEmail
> AND
> [Level].pkLevel = WebUser.fkLevel ) as mysubquery,
webUserArchive
>WHERE
> mysubquery.pkWebUser =* WebUserArchive.fkWebUser
Here's the exact same query, using ANSI syntax:
>FROM
> (SELECT WebUser.*,
> Email.*,
> [Level].*
FROM WebUser INNER JOIN Email ON WebUser.fkEmail = Email.pkEmail
INNER JOIN [Level] ON WebUser.fkLevel = [Level].pkLevel
> WHERE
> WebUser.PartNum IS NOT NULL ) as mysubquery
RIGHT OUTER JOIN webUserArchive
ON mysubquery.pkWebUser = WebUserArchive.fkWebUser
|----------------------+--------------------------------------------------|
|Eric A. Laney |Too much of anything, even love, isn't necessarily|
|Systems Architect |a good thing. |
|Verizon Security | |
|Voice: 813.987.1416 | |
|Pager: 888.551.3718 | Kirk, "The Trouble with Tribbles," stardate|
| | 4525.6.|
|----------------------+--------------------------------------------------|
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
