Skip to main content
K
kevlray57
Participant
July 16, 2008
Question

Multple Joins

  • July 16, 2008
  • 1 reply
  • 392 views
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.
This topic has been closed for replies.

1 reply

N
Newsgroup_User
Inspiring
July 16, 2008
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.
P
paross1
Participating Frequently
July 17, 2008
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices