Multple Joins

[kevlray57]

I have the following as a query on my web page:
SELECT cast(CHG_ACTUAL_AMT as char) as Amount, CHG_TRAN_CD as trans , CHG_SEQ_NBR as seq, CHG_DT_OF_SERVICE as DOS, CHG_POST_DT as PST, CHG_CURRENT_FC as fc, CHG_PERF_DEPT_CD AS dept, CHG_PERF_PHYS_NBR as phys, CHG_PROC_CD as pcode, DISPLAY_NM AS pname, CHG_PAT_NBR AS pnum
FROM AR_CHARGE AS ar
LEFT outer JOIN MPI_XREF as mx on AR.CHG_CUST_NBR = mx.CUST_NBR and AR.CHG_PAT_NBR = mx.PAT_NBR
join MPI as mi on MPI_XREF.MPI_NBR = mi.MPI_NBR and MPI_XREF.MPI_SET = mi.MPI_SET
WHERE CHG_GUAR_NBR = #url.ID#
and CHG_CUST_NBR = 04885

AND MPI_XREF.PAT_NBR <> 0
GROUP BY CHG_SEQ_NBR, CHG_ACTUAL_AMT, CHG_TRAN_CD, CHG_DT_OF_SERVICE, CHG_POST_DT, CHG_CURRENT_FC, CHG_PERF_DEPT_CD, CHG_PERF_PHYS_NBR, CHG_PROC_CD, DISPLAY_NM, CHG_PAT_NBR

As one can see I have a left outer join using two fields and a inner join using two fields. The error I get is:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'MPI_XREF' does not match with a table name or alias name used in the query

I do not know why it does not like my table MPI_XREF

Thanks.

[Newsgroup_User]

Well you seem to have in extra 'AND' in your multiple field join:
"as mi on MPI_XREF.MPI_NBR = mi.MPI_NBR *and* MPI_XREF.MPI_SET *AND*
mi.MPI_SET"

I would guess the second and should be an '=' or some other comparison
operator. I would also suggest a set of parentheses or two or organize
that logic and make it little clearer what belongs to what.

A second possible source of the error, if the above is just anh e-mail
type or something, is if the #url.ID# variable is providing an illegal
string for SQL syntax, it would be 'near' the first AND in the WHERE clause.

[paross1]

How about INNER JOIN instead of just JOIN? Also, you are very inconsistent in your use of table aliases. Does this work any better?

SELECT CAST(ar.CHG_ACTUAL_AMT as char) as Amount,
ar.CHG_TRAN_CD as trans ,
ar.CHG_SEQ_NBR as seq,
ar.CHG_DT_OF_SERVICE as DOS,
ar.CHG_POST_DT as PST,
ar.CHG_CURRENT_FC as fc,
ar.CHG_PERF_DEPT_CD AS dept,
ar.CHG_PERF_PHYS_NBR as phys,
ar.CHG_PROC_CD as pcode,
ar.DISPLAY_NM AS pname,
ar.CHG_PAT_NBR AS pnum
FROM AR_CHARGE ar
LEFT JOIN MPI_XREF mx on ar.CHG_CUST_NBR = mx.CUST_NBR
AND ar.CHG_PAT_NBR = mx.PAT_NBR
AND mx.PAT_NBR <> 0
INNER JOIN MPI mi on mx.MPI_NBR = mi.MPI_NBR
AND mx.MPI_SET = mi.MPI_SET
WHERE ar.CHG_GUAR_NBR = #url.ID#
AND ar.CHG_CUST_NBR = 04885
GROUP BY ar.CHG_SEQ_NBR, ar.CHG_ACTUAL_AMT,
ar.CHG_TRAN_CD, ar.CHG_DT_OF_SERVICE,
ar.CHG_POST_DT, ar.CHG_CURRENT_FC, ar.CHG_PERF_DEPT_CD,
ar.CHG_PERF_PHYS_NBR, ar.CHG_PROC_CD, ar.DISPLAY_NM, ar.CHG_PAT_NBR

Phil