I think you are really close.
It is late, and I'm not necessarily thinking right.
The webuserarchive may not exist. If it doesn't, nothing will be
returned when you perform an inner join (which is what you do)
I'm a little rusty on the syntax, but..
SELECT DISTINCT
WebUser.pkWebUser,
WebUser.username,
Email.email,
[Level].USD,
WebUserArchive.dtUpdated
FROM
( WebUser,
Email,
[Level],
WHERE
WebUser.PartNum IS NOT NULL
AND
WebUser.fkEmail = Email.pkEmail
AND
[Level].pkLevel = WebUser.fkLevel ) outer join webUserArchive on
WebUser.pkWebUser =* WebUserArchive.fkWebUser
Which should return all the information you are asking for, and if it
exists the dtupdated field from the web user archive. It might be a
cleaner syntax if you were to set up the from clause like this:
from (((WebUser Join Email on WebUser.fkEmail = Email.pkEmail)
Join [Level] on [Level].pkLevel = WebUser.fkLevel)
outer join webUserArchive on
WebUser.pkWebUser =* WebUserArchive.fkWebUser)
But, anyway.. that should be the general gist of it.
At 12:45 PM 4/29/2002 +0800, you wrote:
>Hi all,
>
>I have the following SQL Server query (I've simplified it a little to
>save space):
>
>SELECT DISTINCT
> WebUser.pkWebUser,
> WebUser.username,
> Email.email,
> [Level].USD,
> WebUserArchive.dtUpdated
>FROM
> WebUser,
> Email,
> [Level],
> WebUserArchive
>WHERE
> WebUser.PartNum IS NOT NULL
>AND
> WebUser.fkEmail = Email.pkEmail
>AND
> [Level].pkLevel = WebUser.fkLevel
>AND
> WebUser.pkWebUser =* WebUserArchive.fkWebUser
>
>This gives me the following error message:
>
>The table 'WebUser' is an inner member of an outer-join clause. This is
>not allowed if the table also participates in a regular join clause.
>
>What I am trying to do is pull out ALL the matching records in WebUser,
>including the field from WebUserArchive if it exists (most WebUser
>records do not have a corresponding WebUserArchive record). Am I going
>about this the wrong way?
>
>Thanks,
>Kay.
>______________________________________________________
>Kay Smoljak Web Developer PerthWeb Pty Ltd
>
>Level 9/105 St George's Tc - Perth - Western Australia
>Ph: (08) 9226 1366 Fax: (08) 9226 1375
>
>www.perthweb.com.au developer.perthweb.com.au
>
>
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
