Запрос. 2 связаные таблицы. Настандартное обьединение. КАК?

[KSilver]

Помогите. Попробовал join не помогает, union помогает но не совсем.
В общем имеем 2 таблицы.

таблица основная
ID NAME PHONE
1 vasya 44
2 petya 33
3 fedya 44
4 masha 44

таблица 2 содержит некоторую информацию, допустим какой человек сколько раз был судим в каком году, код правонарушения (связь между таблицами по полю ID)

ID CAUSE YEARS
1 456 1999
1 457 2001
2 456 2001

нужно создать запрос (ХП не предлагать, именно запрос)
который возвращает значения из 1 таблицы, и еще одно поле вычисляемое в котором будет 1-если есть записи по судимости, и 0-если нету.
Еще чтобы была возможность сортировки результата.

К примеру хочу найти всю инфу по людяи чей телефон 44 и еще узнать был ли он судим или нет.Отсортированый по имени.
Результат такой

ID NAME PHONE SUDIM
3 fedya 44 0
4 masha 44 0
1 vasya 44 1

[Dimitry Sibiryakov]

Ну и какие проблемы? OUTER JOIN + GROUP BY + COUNT(S.ID) as SUDIM

[avenger]

Если Firebird >= 1.5 тогда можно так:

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

SELECT
    T1.ID, T1.NAME, T1.PHONE,
    CASE
        WHEN COUNT(T2.ID) > 0 THEN 1 ELSE 0
    END AS SUDIM
FROM TABLE1 T1 LEFT JOIN TABLE2 T2 ON T1.ID = T2.ID
GROUP BY T1.ID, T1.NAME, T1.PHONE
ORDER BY T1.NAME

Код не проверял, но по логике должно работать.

С уважением, Иван.

[KSilver]

Ну и какие проблемы? OUTER JOIN + GROUP BY + COUNT(S.ID) as SUDIM

Вобщем это почти что надо. Влт чтл получилось.

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

SELECT t1.id, count(t2.id) as sudim
FROM table1 t1 left outer join table2 t2
on (t1.ID=t2.id)
WHERE
t1.phone LIKE "44%"
group by t1.id
/*ORDER BY t1.NAME*/

ID SUDIM
3 0
4 0
1 2

а надо типа

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

SELECT [b]t1.*[/b], count(t2.id) as sudim
FROM table1 t1 left outer join table2 t2
on (t1.ID=t2.id)
WHERE
t1.phone LIKE "44%"
group by t1.id
[b]ORDER BY t1.NAME[/b]

ID NAME PHONE SUDIM
3 fedya 44 0
4 masha 44 0
1 vasya 44 1

то есть результат не будет содержать инфу о имени и номере телефона (и еще куча других полей типа фамилия, ...) и отсортировать не получится.
попытка добавить в список полей остальные поля проваливается так как стоит group by.

зы firebird 1.0 dialect 1
на переход на 1.5 нехватает человеко-ресурсов

[Dimitry Sibiryakov]

А сделать GROUP BY остальным полям как предлагал avenger запрещает религия?

[avenger]

зы firebird 1.0 dialect 1
на переход на 1.5 нехватает человеко-ресурсов

А FB1.5 нужен только для того, что бы вернуть SUDIM = {0,1}, т.е. в моём случае воспользоваться функцией CASE

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

CASE 
   WHEN COUNT(T2.ID) > 0 THEN 1 ELSE 0 
END AS SUDIM 

А если для тебя не важно, то можешь вернуть количество судимостей и дополнительную информацию так:

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

SELECT 
    T1.ID, T1.NAME, T1.PHONE, COUNT(T2.ID)
FROM TABLE1 T1 LEFT JOIN TABLE2 T2 ON T1.ID = T2.ID 
GROUP BY T1.ID, T1.NAME, T1.PHONE 
ORDER BY T1.NAME

С уважением, Иван.

[KSilver]

А сделать GROUP BY остальным полям как предлагал avenger запрещает религия?

вобщето не запрещает, , но остальных полей 80 штук (на примере я ж не привел прям весь список), и хотелось бы select со звездочкой (так как количество полей может меняться), но видно не судьба.

select в тексте програмы один, только когда загоняю текст запроса в компонент то подставляю то одну таблицу (80 полей), то вторую (84), поля выбирались звездочкой, и не надо было следить за ними.
Теперь вот как я вижу надо будет еще и за полями в тексте запроса следить, в двух местах: в select и в group by.

avenger, case хорошо, но мне действительно не важно 1 или 21, главное ноль или не ноль . спасибо.

[Ivan_Pisarevsky]

Интересно, как юзер будет за раз полторы сотни полей обозревать? Может их и отбирать не стоит?