get all mailing addresses with no value for shipping address

[eHigh]

In the query below, I'm trying to get the mail address and the shipping address. Both addresses are in the ADDRESS table. However some Organizations have mail addresses but not shipping addresses. I would like to get all the mailing addresses even though there is no matching shipping address. I get the following error using the syntax below:

ERROR 1066: Not unique table/alias: 'mail'

How can I get all the mailing addresses even though there is no matching shipping address?

SELECT ORGANIZATION_NAME, mail.ADDRESS, ship.ADDRESS
FROM ORGANIZATION o, ORGANIZATION_TYPE ot, SCHEME_VALUE sv,
ADDRESS mail, ADDRESS ship, ADDRESS_ROLE mail_ar, ADDRESS_ROLE ship_ar
LEFT JOIN mail ON mail.ADDRESS_ID = ship.ADDRESS_ID
WHERE RGANIZATION_ID = ot.ORGANIZATION_ID
AND ot.SCHEME_VALUE_ID = sv.SCHEME_VALUE_ID
AND RGANIZATION_ID = mail_ar.ORGANIZATION_ID
AND RGANIZATION_ID = ship_ar.ORGANIZATION_ID
AND mail_ar.ADDRESS_ID = mail.ADDRESS_ID
AND ship_ar.ADDRESS_ID = ship.ADDRESS_ID
AND mail_ar.ADDRESS_ROLE_TYPE_ID = 2
AND ship_ar.ADDRESS_ROLE_TYPE_ID = 3
AND mail_ar.PERSON_ID = 0
AND ship_ar.PERSON_ID = 0
AND sv.SCHEME_VALUE_ID = 29
ORDER BY RGANIZATION_NAME;

 

[eHigh]

I meant:

How can I get all the mailing addresses even though there are null or empty shipping address?

Thanks

 

[r937]

try this (untested, obviously):

select ORGANIZATION_NAME
     , mail.ADDRESS
     , ship.ADDRESS 
  from ORGANIZATION o
inner
  join ORGANIZATION_TYPE ot
    on o.ORGANIZATION_ID 
     = ot.ORGANIZATION_ID
inner
  join SCHEME_VALUE sv
    on ot.SCHEME_VALUE_ID 
     = sv.SCHEME_VALUE_ID
left outer
  join ADDRESS_ROLE mail_ar
    on o.ORGANIZATION_ID 
     = mail_ar.ORGANIZATION_ID
   and mail_ar.ADDRESS_ROLE_TYPE_ID 
     = 2 
   and mail_ar.PERSON_ID 
     = 0
left outer
  join ADDRESS mail 
    on mail_ar.ADDRESS_ID 
     = mail.ADDRESS_ID
left outer
  join ADDRESS_ROLE ship_ar 
    on o.ORGANIZATION_ID 
     = ship_ar.ORGANIZATION_ID
   and ship_ar.ADDRESS_ROLE_TYPE_ID 
     = 3
   and ship_ar.PERSON_ID 
     = 0
left outer
  join ADDRESS ship
    on ship_ar.ADDRESS_ID 
     = ship.ADDRESS_ID 
 where sv.SCHEME_VALUE_ID 
     = 29
order 
    by o.ORGANIZATION_NAME

rudy

http://r937.com/

SQL Consulting