Привет!
Есть 5 таблиц
CREATE TABLE subscriber
(
id integer NOT NULL DEFAULT nextval('subscriber_id_seq'::regclass),
object_id integer,
subdivision_id integer,
name character(255),
"position" integer
)
CREATE TABLE subdivision
(
id integer NOT NULL DEFAULT nextval('subdivision_id_seq'::regclass),
object_id integer,
name character(255),
"position" integer
)
CREATE TABLE phone
(
id integer NOT NULL DEFAULT nextval('phone_id_seq'::regclass),
subscriber_id integer,
phone character(64)
)
CREATE TABLE object
(
id integer NOT NULL DEFAULT nextval('object_id_seq'::regclass),
name text,
"position" integer
)
CREATE TABLE email
(
id integer NOT NULL DEFAULT nextval('email_id_seq'::regclass),
subscriber_id integer,
email character(255)
)
Как выполнить поиск по всем таблицам в полях:
subscriber -> name
email -> email
phone -> phone
subdivision - > name
object -> object
subscriber -> name
Пробовал так:
$sqlSearch = "SELECT
subscriber.name,
object.name,
subdivision.name,
phone.phone,
email.email
FROM subscriber
LEFT JOIN phone ON (phone.subscriber_id = subscriber.id)
LEFT JOIN email ON (email.subscriber_id = subscriber.id)
LEFT JOIN object ON (object.id = subscriber.object_id)
LEFT JOIN subdivision ON (subdivision.id = subscriber.subdivision_id)
WHERE
subscriber.name ~*'.$search.' OR
phone.phone ~*'.$search.' OR
email.email ~*'.$search.' OR
object.name ~*'.$search.' OR
subdivision.name ~*'.$search.'";
но данный запрос не заглядывает в object.name и subdivision.name
