# Query Cheatsheet

## This is just Basic Query we still need to improve that!!!

#### Get correlation sku and faktur

SELECT invoice_groups.name, brands.name FROM public.invoice_group_brands 
join invoice_groups on invoice_group_brands.invoice_group_id =  invoice_groups.id 
join brands on invoice_group_brands.brand_id = brands.id order by brands.created_at desc limit 100

#### Get correlation Store with Order Parcel

select order_parcels.*, orders.id, stores.name from order_parcels join orders
on order_parcels.order_id = orders.id join stores
on orders.store_id = stores.id where stores.name = 'TK. GONDOK' order by order_parcels.created_at desc limit 10

#### Get valid payment type per supplier

SELECT pts.id,s."name",pt."name" as valid_payment_type 
FROM payment_type_suppliers pts JOIN suppliers s on 
pts.supplier_id = s.id JOIN payment_types pt on 
pt.id = pts.payment_type_id WHERE pts.status = 'active' and pt.status = 'active';

#### Get order cancel reason by order code

SELECT order_code,order_ref,status,reason as "cancel_reason",op.created_at, 
op.updated_at FROM order_parcels op left join order_cancel_reasons ocr on 
op.order_cancel_reason_id = ocr.id WHERE op.order_code = 'S0100032310181110063';

#### Get Store Mapping with urban vs sales code [TRS]

SELECT ss.id as "ss_id", s.id as "s_id",ss.external_id as "ss_external_id", s.external_id as "s_external_id", 
s.store_code,ss."name" as "store_name",
ss.urban_id as "ss_urban", s.urban_id as "s_urban",
w.external_id,u.full_name,u.user_code as "sales_code" FROM supplier_stores ss LEFT JOIN users u on 
ss.creator_id = u.id join warehouse_urbans wu on wu.urban_id = ss.urban_id
join stores s on s.id = ss.store_id
join warehouses w on w.id = wu.warehouse_id WHERE ss.external_id = '16A00508' 
and w.supplier_id = '1'

#### Get correlation users with their role

select users.full_name, roles.role from users join user_roles on 
users.id = user_roles."userId" join roles on
user_roles."roleId" = roles.id where users.mobile_phone_no = '082217574401'

#### Get Warehouses External ID from Order Code

select op.id,order_code,op.status,w.external_id from order_brand_catalogues obc 
left join warehouses w on w.id = obc.warehouse_id 
left join order_brands ob on ob.id = obc.order_brand_id 
left join order_parcels op on op.id = ob.order_parcel_id where order_brand_id in 
(select id from order_brands where order_parcel_id in 
(select id from order_parcels where order_code = 'S0100182312151121786'))

#### New Order Issue

select s.store_id,st.creator_id,s.external_id, st.store_code,s.name,u.id,u.city, u.district, u.urban,op.order_code, op.status,op.portfolio_id, w.id, w.external_id, w.name, p.name, us.user_code, us.sale_team_id, us.urban_id, st.creator_id
from supplier_stores as s 
join stores as st on st.id=s.store_id
join urbans as u on u.id=s.urban_id 
join warehouse_urbans as wu on wu.urban_id=u.id
join warehouses as w on w.id=wu.warehouse_id
join orders as o on o.store_id=s.store_id
join order_parcels as op on op.order_id=o.id
join portfolios as p on p.id = op.portfolio_id
join users as us on us.id = p.user_id
where w.supplier_id ='1' and op.order_code in ('S010003397481148189')

#### Obc.warehouse_id

select s.store_id,st.creator_id,s.external_id, st.store_code,s.name,u.id,u.city, u.district, u.urban,op.order_code, op.status,op.portfolio_id, w.id, w.external_id, w.name, obc.warehouse_id
from supplier_stores as s 
join stores as st on st.id=s.store_id
join urbans as u on u.id=s.urban_id 
join warehouse_urbans as wu on wu.urban_id=u.id
join warehouses as w on w.id=wu.warehouse_id
join orders as o on o.store_id=s.store_id
join order_parcels as op on op.order_id=o.id
join order_brands as ob on ob.order_parcel_id = op.id
join order_brand_catalogues as obc on obc.order_brand_id = ob.id
where w.supplier_id ='1' and op.order_code in ('S010003401431153591') 

###### tags: `Cheatsheet` `Query`