Сам запрос такой (в формате Oracle, т.е. вместо left join используется (+) ):
- Код: Выделить всё
select {c}.{CLNT_KEY} as {CLIENT_KEY}
{c}.{CLIT_CLIT_KEY} as {CLIENT_TYPE}
{ct}.{CLIT_KEY} as {C_TYPE_KEY}
{ct}.{NAME} as {C_TYPE_NAME}
{c}.{ACCOUNT} as {CLIENT_ACCOUNT}
{c}.{NAME} as {CLIENT_NAME}
{c}.{DOC_TYPE} as {CLIENT_DOCTYPE}
{c}.{DOC_SERIES} as {CLIENT_DOCSERIES}
{c}.{DOC_NUMBER} as {CLIENT_DOCNUMBER}
{c}.{DOC_DATE} as {CLIENT_DOCDATE}
{c}.{DOC_WHO} as {CLIENT_DOCWHO}
{c}.{LANGUAGE} as {CLIENT_LANGUAGE}
{c}.{COUNTRY} as {CLIENT_COUNTRY}
{c}.{AREA} as {CLIENT_AREA}
{c}.{CITY} as {CLIENT_CITY}
{c}.{ADDRESS} as {CLIENT_ADDRESS}
{c}.{PHONE} as {CLIENT_PHONE}
{c}.{FAX} as {CLIENT_FAX}
{c}.{NOTES} as {CLIENT_NOTES}
{cd}.{CLDT_KEY} as {C_DETAIL_KEY}
{cd}.{ADR_COU_COU_KEY} as {C_DETAIL_COUNTRY}
{cd}.{ADR_CIT_CIT_KEY} as {C_DETAIL_CITY}
{cd}.{ADR_STR_STR_KEY} as {C_DETAIL_STREET}
{cd}.{ADR_BUILDING} as {C_DETAIL_BUILDING}
{cd}.{ADR_BUILDING_TYPE} as {C_DETAIL_BUILDINGTYPE}
{cd}.{ADR_APPARTAMENT} as {C_DETAIL_APPARTAMENT}
{cd}.{ADR_APPARTAMENT_TYPE} as {C_DETAIL_APPARTAMENTYPE}
{cd}.{ADR_ROOM} as {C_DETAIL_ROOM}
{cd}.{ADR_ROOM_TYPE} as {C_DETAIL_ROOMTYPE}
{cd}.{DOC_TYPE} as {C_DETAIL_DOCTYPE}
{cd}.{DOC_SERIES} as {C_DETAIL_DOCSERIES}
{cd}.{DOC_NUMBER} as {C_DETAIL_DOCNUMBER}
{cd}.{DOC_DATE} as {C_DETAIL_DOCDATE}
{cd}.{DOC_WHO} as {C_DETAIL_DOCWHO}
{cp}.{PHON_KEY} as {C_PHONE_KEY}
{cp}.{PHONE_COUNTRY} as {C_PHONE_COUNTRY}
{cp}.{PHONE_ZONE} as {C_PHONE_ZONE}
{cp}.{PHONE_NUMBER} as {C_PHONE_NUMBER}
{cp}.{PHONE_TYPE} as {C_PHONE_TYPE}
{acou}.{COU_KEY} as {C_ADR_COU_KEY}
{acou}.{CODE} as {C_ADR_COU_CODE}
{acou}.{NAME} as {C_ADR_COU_NAME}
{acou}.{NAME_RUS} as {C_ADR_COU_NAMERUS}
{acou}.{NAME_ENG} as {C_ADR_COU_NAMEENG}
{aare}.{ARE_KEY} as {C_ADR_ARE_KEY}
{aare}.{NAME} as {C_ADR_ARE_NAME}
{aare}.{TYPE} as {C_ADR_ARE_TYPE}
{aare}.{POSTINDEX} as {C_ADR_ARE_POSTINDEX}
{aare}.{NAME_RUS} as {C_ADR_ARE_NAMERUS}
{aare}.{NAME_ENG} as {C_ADR_ARE_NAMEENG}
{adst}.{DST_KEY} as {C_ADR_DST_KEY}
{adst}.{NAME} as {C_ADR_DST_NAME}
{adst}.{TYPE} as {C_ADR_DST_TYPE}
{adst}.{POSTINDEX} as {C_ADR_DST_POSTINDEX}
{adst}.{NAME_RUS} as {C_ADR_DST_NAMERUS}
{adst}.{NAME_ENG} as {C_ADR_DST_NAMEENG}
{acit}.{CIT_KEY} as {C_ADR_CIT_KEY}
{acit}.{NAME} as {C_ADR_CIT_NAME}
{acit}.{TYPE} as {C_ADR_CIT_TYPE}
{acit}.{POSTINDEX} as {C_ADR_CIT_POSTINDEX}
{acit}.{NAME_RUS} as {C_ADR_CIT_NAMERUS}
{acit}.{NAME_ENG} as {C_ADR_CIT_NAMEENG}
{astr}.{STR_KEY} as {C_ADR_STR_KEY}
{astr}.{NAME} as {C_ADR_STR_NAME}
{astr}.{TYPE} as {C_ADR_STR_TYPE}
{astr}.{NAME_RUS} as {C_ADR_STR_NAMERUS}
{astr}.{NAME_ENG} as {C_ADR_STR_NAMEENG}
from {CLIENTS} c, {CLIENT_TYPES} ct, {CLIENT_DETAILS} cd, {CLIENT_PHONES} cp
, {REF_ADR_COUNTRY} acou
, {REF_ADR_AREA} aare
, {REF_ADR_DISTRICT} adst
, {REF_ADR_CITY} acit
, {REF_ADR_STREET} astr
where {c}.{CLIT_CLIT_KEY} = {ct}.{CLIT_KEY} and {c}.{CLNT_KEY} = {cd}.{CLNT_CLNT_KEY} and {c}.{CLNT_KEY} = {cp}.{CLNT_CLNT_KEY}
and {astr}.{CIT_CIT_KEY}(+) = {acit}.{CIT_KEY}
and {acit}.{DST_DST_KEY}(+) = {adst}.{DST_KEY}
and {acit}.{ARE_ARE_KEY}(+) = {aare}.{ARE_KEY}
and {acit}.{COU_COU_KEY}(+) = {acou}.{COU_KEY}
and {adst}.{ARE_ARE_KEY}(+) = {aare}.{ARE_KEY}
and {adst}.{COU_COU_KEY}(+) = {acou}.{COU_KEY}
and {aare}.{COU_COU_KEY}(+) = {acou}.{COU_KEY}
order by #ORDER#
т.е. запрос должен минимально зависеть (вернее, вообще не должен зависеть) от заполненности REF_ADR_* (их могут вообще не заполнять, но клиенты должны быть показаны все).
Результат должен быть примерно такой:
- Код: Выделить всё
from ((({client_detail} as cd
left join
{ref_adr_country} as acou
on {cd}.{COU_COU_KEY} = {acou}.{COU_KEY})
left join
{ref_adr_area} as aare
on {cd}.{ARE_ARE_KEY} = {aare}.{ARE_KEY})
left join
{ref_adr_district} as adst
on {cd}.{DST_DST_KEY} = {adst}.{DST_KEY})
left join
{ref_adr_city} as acit
on {cd}.{CIT_CIT_KEY} = {acit}.{CIT_KEY}
Но он будет куда сложнее, т.к. REF_ADR_* связаны между собой (хотя тут связи избыточные, можно убрать некоторые из них без каких-либо изменений на результате). А также нужно учесть, что помимо cd есть еще c и cp, которые не связаны с REF_ADR_*.
Просто под рукой нет ни Access, ничего другого, а рисовать его в блокноте - слишком много вложений, где-нибудь ошибусь. В Access это сделать легко - нарисовать в макете таблички, связать их, а затем откорректировать связи на left join; сделать полученную строку читаемой уже моя забота.
