Re: join errors

[Christian Mack]

Richard Farmer wrote:
> 
> I am getting a "not unique/error table alias" error when I try to do an
> outer join.  the sql statement is as follows:
> 
>   SELECT DISTINCT billing.account,
>          facilities.fac_name,
>          billing.service,
>          billing.record,
>          billing.workdate,
>          billing.total,
>          user_profiles.last_name,
>          user_profiles.first_name,
>          pi.last_name,
>          pi.first_name,
>          pi.department,
>          pi.phone,
>          pi.address1,
>          pi.address2,
>          pi.address3,
>          pi.mngr_last_name,
>          pi.mngr_first_name,
>          billing.object_code
>     FROM {oj billing  LEFT OUTER JOIN user_profiles  ON billing.email =
> user_profiles.email}, {oj billing  LEFT OUTER JOIN pi  ON billing.pi_email
> = pi.email},
>          facilities
>    WHERE ( billing.facility = facilities.fac_num ) and
>          ( ( billing.facility = :fac ) AND
>          ( billing.workdate >= :d1 ) AND
>          ( billing.workdate <= :d2 ) )
> 
>         I tried using an alias, but I recieved the same error.
>         Can anyone help?
> 
>         Thanks,
>         Richard Farmer

Hi Richard

You get this error, because your syntax is wrong :)
Try:
SELECT DISTINCT billing.account,   
         facilities.fac_name,   
         billing.service,   
         billing.record,   
         billing.workdate,   
         billing.total,   
         user_profiles.last_name,   
         user_profiles.first_name,   
         pi.last_name,   
         pi.first_name,   
         pi.department,   
         pi.phone,   
         pi.address1,   
         pi.address2,   
         pi.address3,   
         pi.mngr_last_name,   
         pi.mngr_first_name,   
         billing.object_code
FROM
	billing
	LEFT JOIN user_profiles ON (billing.email = user_profiles.email)
	LEFT JOIN pi ON (billing.pi_email = pi.email)
	,facilities
WHERE
	( billing.facility = facilities.fac_num ) and  
         ( ( billing.facility = fac ) AND  
         ( billing.workdate >= d1 ) AND  
         ( billing.workdate <= d2 ) )

Tschau
Christian