Выборка основной и дополнительной информации за один запрос

[Foshvad]

Выборка основной и дополнительной информации за один запрос

Необходимо за один запрос выбрать информацию из двух таблиц, связанных уникальным ключем. При этом, одной записи в первой таблице может соответствовать 0 или несколько записей во второй, а конкретизировать запрос надо по нескольким значениям из второй таблицы.

Например.

Есть Таблица пользователей (users) и таблица с дополнительной информацией о пользователях. (info)

Users:


id_user | login | pass
----------------------
1 | log1 | pas1
2 | log2 | pas2
3 | log3 | pas3



Info:


id_user | name | value
--------------------------
1 | icq | 12345678
1 | email | [email protected]
2 | tel | 223344


В результате выборки необходимо, чтоб в одной строке содержалась вся информация о пользователе.

Например:


id_user | login | pass | icq | email | tel
----------------------------------------------------------
1 | log1 | pas1 | 12345678 | [email protected] | NULL
2 | log2 | pas2 | NULL | NULL | 223344
3 | log3 | pas3 | NULL | NULL | NULL



Через JOIN к сожалению не получается


SELECT *
FROM users
LEFT JOIN info ON info.id_user = users.id_user


Выбирается:


id_user | login | pass | id_user | name | value
----------------------------------------------------
1 | log1 | pas1 | 1 | tel | 23423423
1 | log1 | pas2 | 1 | email | [email protected]
2 | log2 | pas2 | 2 | tel | 12345235
3 | log3 | pas3 | 3 | NULL | NULL




То есть информация по пользователям дублируется согласно таблицы info.

При этом возникает проблема с конкретизацией выборки по нескольким дополнительным параметрам (в данном случае, например, по выбору пользователей, у которых определенный email и tel)

 

[Demiurg]

Если тебе просто нужна информация, то получаешь в том виде, в котором получил, а дальше уже скриптами выводишь так, как хочется.

Если нужны ограничения:


SELECT users.* , tel.value phone , email.value mail
FROM users
JOIN info tel ON tel.name = 'tel' and tel.id_user = users.id_user
JOIN info email ON tel.name = 'email' and email.id_user = users.id_user
where phone = '...' and mail = '...'

 

[Foshvad]

Demiurg

JOIN info tel ON tel.name = 'tel' and tel.id_user = users.id_user
JOIN info email ON tel.name = 'email' and email.id_user = users.id_user

при таком запроце не будет выбрана остальная информация о пользователях.

А не обходимо выбрать ВСЮ информацию о пользователях, у которых определенные значения полей tel и email

 

[Demiurg]

SELECT * , tel.value phone , email.value mail
FROM users
LEFT JOIN info ON info.id_user = users.id_user and info.name not in ('tel' , 'email')
JOIN info tel ON tel.name = 'tel' and tel.id_user = users.id_user
JOIN info email ON tel.name = 'email' and email.id_user = users.id_user
where phone = '...' and mail = '...'

 

[Foshvad]

tel и email - это значения в колонке name таблице info.

Вот дам двух таблиц для примера:


CREATE TABLE info (
id_user smallint(3) unsigned NOT NULL default '0',
name varchar(50) NOT NULL default '',
value varchar(50) NOT NULL default ''
) TYPE=MyISAM;

INSERT INTO info VALUES (1, 'tel', '23423423');
INSERT INTO info VALUES (2, 'tel', '12345235');
INSERT INTO info VALUES (1, 'email', '[email protected]');


CREATE TABLE users (
id_user smallint(3) unsigned NOT NULL default '0',
login varchar(50) NOT NULL default '',
pass varchar(50) NOT NULL default ''
) TYPE=MyISAM;

INSERT INTO users VALUES (1, 'log1', 'pas1');
INSERT INTO users VALUES (2, 'log2', 'pas2');
INSERT INTO users VALUES (3, 'log3', 'pas3');

 

[Demiurg]

это я все понял.
что в моем запрос смущает ?

 

[Foshvad]

Demiurg

что в моем запрос смущает ?

Как минимум это:

Unknown column 'phone' in 'where clause'

 

[Demiurg]

замени на tel.value
запрос надо понимать а не втупую копировать.

 

[Foshvad]

Конечно же я не понял запрос "в тупую".

SELECT *
FROM users
LEFT JOIN info ON info.id_user = users.id_user AND info.name NOT
IN ('tel', 'email')
JOIN info tel ON tel.name = 'tel' AND tel.id_user = users.id_user
JOIN info email ON tel.name = 'email' AND email.id_user = users.id_user
WHERE tel.value = '23423423'

ответ MySQL:

Unknown column 'info.id_user' in 'on clause'

Быть может я не до конца понимаю сам запрос - ты не мог бы привести пример работающего запроса?

Спасибо!

 

[Demiurg]

что именно в этом запрос не понятно ?

 

[Foshvad]

Demiurg

что именно в этом запрос не понятно ?

непонятно, почему обращение идет к значению переменной, как к названию таблиц? (tel.value , tel.name)

если не сложно, поясни плз на примере работающего запроса.

 

[Demiurg]

это не значение переменной это просто алиас
JOIN info tel ON tel.name = 'tel' AND tel.id_user = users.id_user

вместо tel можно использовать все, что угодно

JOIN info telephone ON telephone.name = 'tel' AND telephone.id_user = users.id_user

 

[Foshvad]

Вот это работает, большое спасибо

SELECT *
FROM users
LEFT JOIN info ON info.id_user = users.id_user
JOIN info telephone ON telephone.name = 'tel' AND telephone.id_user = users.id_user
JOIN info mail ON mail.name = 'email' AND mail.id_user = users.id_user
WHERE telephone.value = '03' and mail.value = 'qqq'

Последний вопрос: я правильно понимаю, что для конкретизации каждого поля необходимо будет прописывать отдельный

JOIN info алиас_поля ON алиас_поля.name = 'имя_поля' AND алиас_поля.id_user = users.id_user

?

Не создаст ли это избыточной нагрузки на базу?

 

[Demiurg]

>Не создаст ли это избыточной нагрузки на базу?
создаст, но зачем надо было выносить в отдельную таблицу ?
Хотя при настроеных индексах нагрузки будут не большие.

 

[Foshvad]

Оп. небольшая проблемка.

Для постраничного вывода необходимо применить LIMIT к числу выбираемых пользователей (а не записей в целом)

Это можно как-то сделать?

 

[Demiurg]

можно с лимитом выбирать пользователей во временную таблицу, а потом уже её использовать в запросе. Пока ничего другого в голову не приходит.

 

[Foshvad]

Demiurg

можно с лимитом выбирать пользователей во временную таблицу, а потом уже её использовать в запросе. Пока ничего другого в голову не приходит.

вот это уже грустно
Временые таблицы будет использовать явно не оптимально

 

[Demiurg]

>Временые таблицы будет использовать явно не оптимально
почему ?

 

[Foshvad]

Demiurg

почему ?

Потому такую таблицу придется делать либо при каждом просмотре таблицы, либо обновлять всю таблицу, когда меняется одно из значений.

 

[Demiurg]

и что ? временные таблицы создаются в памяти, и уничтожаются по закрытии сессии. Я не уверен, но помоему при некоторых запросах mysql сама создает временные таблицы.