Запросы SQL

[dnclive]

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

[dnclive]

запрос для view_tab_customer

выбирает всех контрагентов + id_pick - будет число если контрагент закреплен (будет выведен в топе или если запрос пуст при отображении формы)

• выводит прикрепленный к данному контрагенту основной адрес если такой есть

select 
    c.*,
    p.id as tab_pick_id,
    f.freq as freq
from 
    tab_customer c
    left join tab_pick p on c.id=p.tab_customer_id and p.deleted is null
    left join tab_freq f on c.id=f.tab_customer_id and f.deleted is null
where 
    (
        c.deleted is null
        or c.deleted>dateadd(day, -1, getdate())
    )
    --and p.deleted is null

через uid

select 
    c.*,
    p.id as tab_pick_id,
    f.freq as freq
from 
    tab_customer c
    left join tab_pick p on c.uid=p.tab_customer_uid and p.deleted is null
    left join tab_freq f on c.uid=f.tab_customer_uid and f.deleted is null
where 
    (
        c.deleted is null
        or c.deleted>dateadd(day, -1, getdate())
    )
    --and p.deleted is null

[dnclive]

запрос для view_tab_customer_address

select 
    c.*,
    p.id as tab_pick_id,
    f.freq as freq, 
    (
        select top 1 name 
        from 
            tab_relat_391 r
            left join tab_address a on r.tab_address_id=a.id and a.deleted is null
            left join tab_pick p on a.id=p.tab_address_id and p.deleted is null
        where
            r.deleted is null
            and r.tab_customer_id=c.id
    ) as picked_address_name
from 
    tab_customer c
    left join tab_pick p on c.id=p.tab_customer_id and p.deleted is null
    left join tab_freq f on c.id=f.tab_customer_id and f.deleted is null

where 
    (
        c.deleted is null
        or c.deleted>dateadd(day, -1, getdate())
    )
    --and p.deleted is null

   and c.name like '%новый%'

тоже самое но со связью через guid

select 
    c.*,
    p.id as tab_pick_id,
    f.freq as freq, 
    (
        select top 1 name 
        from 
            tab_relat_391 r
            left join tab_address a on r.tab_address_uid=a.uid and a.deleted is null
            left join tab_pick p on a.uid=p.tab_address_uid and p.deleted is null
        where
            r.deleted is null
            and r.tab_customer_uid=c.uid
        order by r.tab_id desc
    ) as picked_address_name
from 
    tab_customer c
    left join tab_pick p on c.uid=p.tab_customer_uid and p.deleted is null
    left join tab_freq f on c.uid=f.tab_customer_uid and f.deleted is null

where 
    (
        c.deleted is null
        or c.deleted>dateadd(day, -1, getdate())
    )
    --and p.deleted is null

   and c.name like '%эпифанцев%'