iBase.ru Forum

Код: Выделить всё

CREATE TABLE TOWN (
    ID_TOWN       INTEGER NOT NULL,
    ID_REGION     INTEGER NOT NULL,
    ID_DISTRICT   INTEGER,
    ID_TOWN_TYPE  INTEGER NOT NULL,
    NAME          VARCHAR(40) NOT NULL,
    ZIP_CODE      VARCHAR(6)
);

ALTER TABLE TOWN ADD CONSTRAINT PK_TOWN PRIMARY KEY (ID_TOWN);

ALTER TABLE TOWN ADD CONSTRAINT FK_DISTRICT__TOWN FOREIGN KEY (ID_DISTRICT) REFERENCES DISTRICT (ID_DISTRICT) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE TOWN ADD CONSTRAINT FK_REGION__TOWN FOREIGN KEY (ID_REGION) REFERENCES REGION (ID_REGION) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE TOWN ADD CONSTRAINT FK_TOWN_TYPE__TOWN FOREIGN KEY (ID_TOWN_TYPE) REFERENCES TOWN_TYPE (ID_TOWN_TYPE);

CREATE INDEX IDX_TOWN_NAME ON TOWN (NAME);

Код: Выделить всё

SELECT
    T.ID_TOWN
  , R.ID_REGION
  , D.ID_DISTRICT
  , TT.SHORT_NAME
  , T.ID_TOWN_TYPE
  , T.NAME
  , T.ZIP_CODE
  , R.NAME AS REGION_NAME
  , COALESCE(D.NAME, '-') AS DISTRICT_NAME
FROM TOWN T LEFT JOIN DISTRICT D ON T.ID_DISTRICT = D.ID_DISTRICT
            LEFT JOIN REGION R ON T.ID_REGION = R.ID_REGION +0
            LEFT JOIN TOWN_TYPE TT ON T.ID_TOWN_TYPE = TT.ID_TOWN_TYPE +0
WHERE UPPER(T.NAME) LIKE UPPER(:pWord) || '%'

Код: Выделить всё

SELECT
       T.ID_TOWN
     , TT.SHORT_NAME
     , T.ID_TOWN_TYPE
     , T.NAME
     , T.ZIP_CODE
  FROM TOWN_TYPE TT RIGHT JOIN TOWN T ON T.ID_TOWN_TYPE = TT.ID_TOWN_TYPE
 WHERE UPPER(T.NAME) LIKE UPPER(:pWord) || '%'

Код: Выделить всё

SELECT  T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
  FROM TOWN T WHERE UPPER(T.NAME) LIKE UPPER('М') || '%'

SELECT T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE UPPER(T.NAME) LIKE 'М%'

SELECT  T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE T.NAME LIKE 'М' || '%'

SELECT T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE UPPER(T.NAME) STARTING 'М'

Код: Выделить всё

SELECT  T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE T.NAME LIKE 'М%'

SELECT  T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE T.NAME STARTING 'М'