join issue with query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My query is only returning people that have a business unit number. It does
not return patients where there may not be a business unit with just a blank
value. I want to diaply the ID, Name and Business Unit on report. Business
Unit may or may not be populated. thanks

"SELECT
MDR.PAT_MV.PAT_ID,
MDR.PAT_MV.PAT_FULL_NAME,
BUSINESSUNIT.PAT_GDLN_VAL_TXT
FROM
MDR.PAT_MV,
MDR.PAT_GDLN_VAL_BRIDGE BUSINESSUNIT
WHERE
( MDR.PAT_MV.PAT_DIM_PID=BUSINESSUNIT.PAT_DIM_PID(+)
and BUSINESSUNIT.GDLN_DEF_CTGY_CODE(+)='*'
and BUSINESSUNIT.GDLN_DEF_CODE(+)='MUBU' )
AND ( BUSINESSUNIT.GDLN_DEF_CODE='MUBU' )"
 
JRS said:
My query is only returning people that have a business unit number. It does
not return patients where there may not be a business unit with just a blank
value. I want to diaply the ID, Name and Business Unit on report. Business
Unit may or may not be populated. thanks

"SELECT
MDR.PAT_MV.PAT_ID,
MDR.PAT_MV.PAT_FULL_NAME,
BUSINESSUNIT.PAT_GDLN_VAL_TXT
FROM
MDR.PAT_MV,
MDR.PAT_GDLN_VAL_BRIDGE BUSINESSUNIT
WHERE
( MDR.PAT_MV.PAT_DIM_PID=BUSINESSUNIT.PAT_DIM_PID(+)
and BUSINESSUNIT.GDLN_DEF_CTGY_CODE(+)='*'
and BUSINESSUNIT.GDLN_DEF_CODE(+)='MUBU' )
AND ( BUSINESSUNIT.GDLN_DEF_CODE='MUBU' )"
Click to expand...


You have a plethora of indecipherable syntax errors there.

The best guess I can make at this point is that you have a
lot of names that include funky (non-alphanumeric)
characters. The least you need to do is enclose the names
in square brackets so we and Access can figure out which
objects are which.
 
JRS said:
My query is only returning people that have a business unit number. It does
not return patients where there may not be a business unit with just a blank
value. I want to diaply the ID, Name and Business Unit on report. Business
Unit may or may not be populated. thanks

"SELECT
MDR.PAT_MV.PAT_ID,
MDR.PAT_MV.PAT_FULL_NAME,
BUSINESSUNIT.PAT_GDLN_VAL_TXT
FROM
MDR.PAT_MV,
MDR.PAT_GDLN_VAL_BRIDGE BUSINESSUNIT
WHERE
( MDR.PAT_MV.PAT_DIM_PID=BUSINESSUNIT.PAT_DIM_PID(+)
and BUSINESSUNIT.GDLN_DEF_CTGY_CODE(+)='*'
and BUSINESSUNIT.GDLN_DEF_CODE(+)='MUBU' )
AND ( BUSINESSUNIT.GDLN_DEF_CODE='MUBU' )"
Click to expand...


MS Access won't let me create a Table with period "." in the name.

Which database application are you using?


Sincerely,

Chris O.
 
My query is only returning people that have a business unit number. It does
not return patients where there may not be a business unit with just a blank
value. I want to diaply the ID, Name and Business Unit on report. Business
Unit may or may not be populated. thanks

"SELECT
MDR.PAT_MV.PAT_ID,
MDR.PAT_MV.PAT_FULL_NAME,
BUSINESSUNIT.PAT_GDLN_VAL_TXT
FROM
MDR.PAT_MV,
MDR.PAT_GDLN_VAL_BRIDGE BUSINESSUNIT
WHERE
( MDR.PAT_MV.PAT_DIM_PID=BUSINESSUNIT.PAT_DIM_PID(+)
and BUSINESSUNIT.GDLN_DEF_CTGY_CODE(+)='*'
and BUSINESSUNIT.GDLN_DEF_CODE(+)='MUBU' )
AND ( BUSINESSUNIT.GDLN_DEF_CODE='MUBU' )"
Click to expand...

This looks like you're using ORACLE syntax. Access can handle outer
joins, but it uses ANSI 92 Join syntax rather than Oracle's
proprietary (+) syntax.

Try

"SELECT
MDR.PAT_MV.PAT_ID,
MDR.PAT_MV.PAT_FULL_NAME,
BUSINESSUNIT.PAT_GDLN_VAL_TXT
FROM
MDR.PAT_MV LEFTL JOIN
MDR.PAT_GDLN_VAL_BRIDGE BUSINESSUNIT
ON MDR.PAT_MV.PAT_DIM_PID=BUSINESSUNIT.PAT_DIM_PID
WHERE
(BUSINESSUNIT.GDLN_DEF_CTGY_CODE='*'
OR BUSINESSUNIT.GDLN_DEF_CTGY_CODE IS NULL)
AND
(BUSINESSUNIT.GDLN_DEF_CODE='MUBU'
OR BUSINESSUNIT.GDLN_DEF_CODE IS NULL);

This will retreive records where GDLN_DEF_CODE is equal to the literal
asterisk text value, and GDLN_DEF_CODE is equal to MUBU; or records
from MDR where there is no record in BUSINESSUNIT at all.

John W. Vinson[MVP]
 
Back
Top