Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Список контактов

Автор: MrDmitry 31.8.2009, 22:16

Помогите составить sql запрос

Есть 2 таблицы
в одной хронятся пользователи (id,name,email и тд)
в другой хронятся сообщения (id, user_id(кто отправил), recipient_id(кому отправил),messages,data )
нужно сделать контактный лист.
Тоесть чтобы выводились все с кем переписывался user_id с сортировкой по дате

Использую mysql

Автор: Gluttton 31.8.2009, 23:09

Что то вроде такого...

Код


select
    t1.name,
    t2.data
from t1
    inner join t2
    on t1.id=t2.recipient_id
        where t2.user_id='user'
    order by t2.data

где t1 и t2 это таблицы с информацией о пользователях и сообщениях соответственно, а 'user' - пользователь, "для которого" формируется запрос.

Автор: MrDmitry 31.8.2009, 23:36

написал так

Код


SELECT u.name, m.date_send, u.user_id, m.recipient_id FROM user u
             INNER JOIN message m ON u.id=m.recipient_id WHERE u.user_id=:user_id ORDER BY m.date_send

и не катит. или я где то ошибся???

PS :user_id глобальная переменная определяющая нашего текущего пользователя

Автор: Gluttton 31.8.2009, 23:43

Цитата

и не катит

Выполняется с ошибкой или возвращает не те данные, которые ожидаються?

Автор: MrDmitry 1.9.2009, 00:21

не каких визуальных ошибок нет. Возвращается пустая страница без данных. Вывожу правельно

Автор: Gluttton 1.9.2009, 00:32

Цитата

не каких визуальных ошибок нет. Возвращается пустая страница без данных. Вывожу правельно

В таком случае, прошу показать на каких тестовых данных тестируется запрос (можно не все, а часть smile

), в следующем виде:
1. Содержимое таблицы пользователей.
2. Содержимое таблицы сообщений.
3. Ожидаемые данные.
4. Полученые даные.

Автор: MrDmitry 1.9.2009, 00:53

messages

Код


id user_id recipient_id date_send theme message state procent 

3 4 3 0   2 сообщение от друкого пользователя N 16 
4 3 3 0   3 сообщение N 16 
5 5 3 0   4 сообщение входящее N 16 
6 6 3 0   5 Входящее сообщение N 16 
12312 5 3 0   7 сообщение N 16 
21312 3 2 0   32432423 
3424234 6 3 0   8 сообщение N 16

user

i

Код


d name lastname login email date_birth procent 

3  1       1      [email protected] 1930-01-01  
4  2       2      [email protected] 1930-01-01 
5  3       3      [email protected] 1930-01-01
6  4       4      [email protected] 1930-01-01

ожидалось что выведется следующий список

1 1
2 2
3 3
4 4

не вывелось ни чего

PS цыфры это фамилии Имя Отчество smile

Автор: MrDmitry 1.9.2009, 03:18

так все разобрался sql работает но выводит не то что нужно.
Вот предствате себе записную книжку в телефоне. Там вить редко когда бывают повторяющиеся сообщения. Мне нужно вывести список всех с кем пользователь обменивался сообщениями. Каждый кто поподает в список выводится 1 раз

(в это скрипте выводились те с кем пользователь переписывался. при чем пользователи повторялись в списки :()

Автор: Akina 1.9.2009, 07:04

Цитата(MrDmitry @ 1.9.2009, 00:36

)

или я где то ошибся???

В синтаксисе. Двоеточие убери.
Но чтобы каждый абонент был в списке только 1 раз - нужно в запрос добавить группировку по абонентам, и брать соотв. дату связи (первую либо последнюю).

Автор: DimW 1.9.2009, 08:36

Цитата(MrDmitry @ 1.9.2009, 03:18

)

Каждый кто поподает в список выводится 1 раз

Код


select distinct
       u.name
      ,u.lastname
      ,m.user_id
  from user u
 inner join message m on u.id = m.recipient_id
 where u.user_id = :user_id
 order by m.date_send

вслучае если у пользователя нет сообшений то результат будет 0 строк, что бы этого избежать, замените INNER JOIN на LEFT JOIN.

Цитата(Akina @ 1.9.2009, 07:04

)

В синтаксисе. Двоеточие убери.

если вы про это ":user_id", то это параметр запроса, только не глобальный как выразился ТС - область его видимости не выходит за пределы данного запроса.

Автор: Akina 1.9.2009, 08:55

Цитата(DimW @ 1.9.2009, 09:36

)

если вы про это ":user_id", то это параметр запроса

Зависит от диалекта - который не указан.

Автор: DimW 1.9.2009, 09:07

Цитата(Akina @ 1.9.2009, 08:55

)

Зависит от диалекта - который не указан.

ну с этим ни кто и не спорит.

Автор: MrDmitry 1.9.2009, 09:55

снова не то

в бд

Код



id user_id recipient_id date_send theme message state procent 
3 4 3 0   2 сообщение от друкого пользователя N 16 
4 3 3 0   3 сообщение N 16 
5 5 3 0   4 сообщение входящее N 16 
6 6 3 0   5 Входящее сообщение N 16 
12312 5 3 0   7 сообщение N 16 
21312 3 2 0   32432423 
3424234 6 3 0   ывавыаыв 
11 6 4 0   ывавыаывавы 
14 4 6 0   выавыавыавы 
17 5 4 0   выавыаываывавы
17 7 4 0   в345
17 8 2 0   выавыаываывавы

выводит

Имя    Фамилия
4             4
5             5

Нужно

Имя    Фамилия
4            4
5            5
6            6

То есть грубо говоря выводится список исходящих сообщений а не контакты (

Автор: Zloxa 1.9.2009, 09:57

Цитата(Akina @ 1.9.2009, 08:55

)

Зависит от диалекта - который не указан.

Цитата(MrDmitry @ 31.8.2009, 22:16

)

Использую mysql

Хотя я бы сказал зависит от способа доступа.

Автор: Gluttton 1.9.2009, 10:03

Цитата

То есть грубо говоря выводится список исходящих сообщений а не контакты (

Цитата

Тоесть чтобы выводились все с кем переписывался user_id с сортировкой по дате

Так всё таки, что же требуется?

Автор: MrDmitry 1.9.2009, 11:07

Я наверное не правильно выразился
Сейчас постараюсь обьяснить

есть 2 списка
в одном выводится список всех сообщений которые пришли нам (Входящие)
в другом выводится список сообщений которые отправил пользователь (Исходящие)
так вот нужно вывести всех пользователей из спиков Входящие,Исходящие, чтоб выводились все пользователи которые попали в эти списки, но выводились 1 раз, тоесть формируется список контактов

вот sql запрос списка "Входящие"

Код


SELECT m_id, uname, u.lastname, m.user_id, m.date_send, m.theme, m.message
FROM messages m, users u WHERE m.recipient_id=:user_id AND u.id=m.user_id

вот sql запрос списка "Исходящие"

Код


SELECT m_id, uname, u.lastname, m.user_id, m.date_send, m.theme, m.message
FROM messages m, users u WHERE m.user_id=:user_id AND u.id=m.recipient_id

Автор: Gluttton 1.9.2009, 11:22

Ну дык, и UNION тебе в руки smile

...
(Между запросами, для объединения их результатов необходимо поместить UNION - в тех случаях, когда необходимо, что бы результаты не повторялись (т.е. отображались только уникальные записи) или UNION ALL - когда необходимо, что бы отображались все данные).

Автор: Akina 1.9.2009, 11:24

Код


select distinct users.id, users.username
from
(
  (
  select sender_id id
  from messages
  where recipient_id=:userID
  )
union
  (
  select recipient_id 
  from messages
  where sender_id=:userID
  )
) mess
inner join users
on users.id=mess.id

Автор: DimW 1.9.2009, 11:26

Код


select u.name, u.lastname, m.user_id
from messages m, users u where m.recipient_id=:user_id and u.id=m.user_id
union
select u.name, u.lastname, m.user_id
from messages m, users u where m.user_id=:user_id and u.id=m.recipient_id

Цитата(Akina @ 1.9.2009, 11:24

)

select distinct users.id, users.username

Akina, в данном случае distinct это излишество.

Автор: MrDmitry 1.9.2009, 11:33

щас попробую

Добавлено через 12 минут и 54 секунды
Сообвственно вот что выдалось
The used SELECT statements have a different number rof colomns

Автор: DimW 1.9.2009, 12:21

Цитата(MrDmitry @ 1.9.2009, 11:33

)

Сообвственно вот что выдалось

собственно покажите что запускали.

Автор: Akina 1.9.2009, 12:28

Цитата(DimW @ 1.9.2009, 12:26

)

в данном случае distinct это излишество

Угу. Но на время выполнения это не повлияет, а направление мысли покажет.

Автор: MrDmitry 1.9.2009, 12:29

Код


SELECT m_id, uname, u.lastname, m.user_id, m.date_send, m.theme, m.message
FROM messages m, users u WHERE m.recipient_id=:user_id AND u.id=m.user_id
UNION
SELECT m_id, uname, u.lastname, m.user_id, m.date_send, m.theme, m.message
FROM messages m, users u WHERE m.user_id=:user_id AND u.id=m.recipient_id

вот так писал

Автор: DimW 1.9.2009, 13:05

Цитата(MrDmitry @ 1.9.2009, 12:29

)

вот так писал

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

Цитата(Akina @ 1.9.2009, 12:28

)

Но на время выполнения это не повлияет

можно было бы проверить и поспорить, но что то лениво smile

(да и БД будет другая)

Автор: MrDmitry 1.9.2009, 13:14

я из его примера не понел что есть
sender_id и mess

Автор: Zloxa 1.9.2009, 13:15

Цитата(Akina @ 1.9.2009, 12:28

)

Но на время выполнения это не повлияет,

В общем случае повлияет.
Лишь в частном, когда на users.id имеет ограничение уникальности, это ограничение включено и валидно, оптимизатор будет иметь основания опустить distinct.

Автор: DimW 1.9.2009, 13:20

Цитата(MrDmitry @ 1.9.2009, 13:14

)

я из его примера не понел что есть
sender_id и mess

ну если recipient_id у вас получатели(входящие), то несложно догадаться что sender_id отправители(исходящие), mess это алиас подзапроса(внимательней посмотрите запрос).

Автор: Zloxa 1.9.2009, 13:30

Цитата(Zloxa @ 1.9.2009, 13:15

)

В общем случае повлияет.

Кстати, пытаясь это продемонстрировать, наткнулся на багу в 11g smile

)
DimW, можешь прогнать на десятке, девятке (у меня дома не стоит)?

Код


Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
Connected as ZLOXA
 
SQL> create table tst(id not null,val) as (select 1 id,2 val from dual
  2                       union all select 1,2 val from dual);
 
Table created
SQL> create index tst$id$idx on tst(id);
 
Index created
SQL> alter table tst add constraint tst$id$unc unique(id) using index tst$id$idx novalidate;
 
Table altered
SQL> select distinct * from tst;
 
        ID        VAL
---------- ----------
         1          2
         1          2
 
SQL>

Автор: DimW 1.9.2009, 13:38

Цитата(Zloxa @ 1.9.2009, 13:30

)

девятке

Код


Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0 
Connected as jdbf
 
SQL> 
SQL> create table tst(id not null,val) as (select 1 id,2 val from dual
  2                                        union all select 1,2 val from dual);
 
Table created
SQL> create index tst$id$idx on tst(id);
 
Index created
SQL> alter table tst add constraint tst$id$unc unique(id) using index tst$id$idx novalidate;
 
Table altered
SQL> select distinct * from tst;
 
        ID        VAL
---------- ----------
         1          2

есть мысли почему так?

Автор: Zloxa 1.9.2009, 13:44

Цитата(DimW @ 1.9.2009, 13:38

)

есть мысли почему так?

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

Собсно я пытался покзать результат работы этого скрипта, но на 11м он мне вернул одинаковые планы, ввиду бага. На десятке, девятке, уверен планы будут разные:

Код


create table tst(id not null,val) as (select 1 id,2 val from dual 
                     union all select 1,2 val from dual);
create index tst$id$idx on tst(id);                     
alter table tst add constraint tst$id$unc unique(id) using index tst$id$idx novalidate;
explain plan for select distinct id from tst;
select * from table(dbms_xplan.display());
delete from tst where rownum =1;
alter table tst modify constraint  tst$id$unc validate;
explain plan for select distinct id from tst;
select * from table(dbms_xplan.display());
drop table tst;

Автор: DimW 1.9.2009, 13:56

Цитата(Zloxa @ 1.9.2009, 13:44

)

Баг, не иначе... ;)

да ладно это мелочи посравнению с - http://www.google.ru/search?hl=ru&rlz=1G1GGLQ_RURU339&newwindow=1&q=%D0%B8%D0%BD%D0%B4%D0%B8%D1%8F+%D0%BF%D0%BE%D1%82%D0%B5%D1%80%D1%8F%D0%BB%D0%B0+%D1%81%D0%BF%D1%83%D1%82%D0%BD%D0%B8%D0%BA&lr=&aq=f&oq= smile

Автор: MrDmitry 1.9.2009, 14:03

блин я туплю жестко
У меня же нет столбца sender_id
что за место этого подставлять? ((((

Добавлено через 3 минуты и 53 секунды
и еще на что заменить ORDER BY чтоб сортировка была не по убыванию а возростанию
Тоесть чтоб сначало выводилось самое ранне сообщение затем меннее ранее и т.д

Автор: MrDmitry 1.9.2009, 14:22

сорри глюканула 2 раза наверно отправить сообщение нажал

Автор: DimW 1.9.2009, 14:23

Цитата(MrDmitry @ 1.9.2009, 14:03

)

что за место этого подставлять?

m.user_id

Цитата(MrDmitry @ 1.9.2009, 14:03

)

и еще на что заменить ORDER BY чтоб

order by ПОЛЕ asc - по возрастанию
order by ПОЛЕ desc - по убыванию

по умолчанию должно от меньшего к большему.

Автор: MrDmitry 1.9.2009, 14:57

Все всем огромное спасибо smile