sql - JOIN table with itself, and filter rows from each table with bitwise operation -
i have following table,
-- generated pg_dump, constraints missing create table articulos_factura_venta ( fila integer not null, cantidad integer not null, color integer not null, talla integer not null, estado integer default 2 not null, origen integer, factura integer not null, articulo integer not null, precio integer not null, vendedor integer, anulado boolean default false, iva double precision default 12.0, fecha date default ('now'::text)::date not null ); and contains following rows1
fila | cantidad | color | talla | estado | origen | factura | articulo | precio | vendedor | anulado | iva | fecha ------+----------+-------+-------+--------+--------+---------+----------+--------+----------+---------+-----+------------ 0 | 1 | 0 | 3 | 6 | 18 | 28239 | 1325 | 455 | 6 | f | 0 | 2015-04-22 1 | 1 | 0 | 2 | 6 | 93 | 28239 | 2071 | 615 | 6 | f | 0 | 2015-04-22 2 | 1 | 0 | 49 | 6 | 76 | 28239 | 2013 | 545 | 6 | f | 0 | 2015-04-22 3 | 1 | 0 | 78 | 6 | 85 | 28239 | 2042 | 235 | 6 | f | 0 | 2015-04-22 4 | 1 | 0 | 49 | 6 | 81 | 28239 | 2026 | 615 | 6 | f | 0 | 2015-04-22 5 | 1 | 0 | 50 | 6 | 90 | 28239 | 2051 | 755 | 6 | f | 0 | 2015-04-22 6 | 1 | 0 | 1 | 38 | 21 | 28239 | 1780 | 495 | 6 | f | 0 | 2015-04-22 7 | 1 | 15 | 2 | 38 | 16 | 28239 | 1323 | 845 | 6 | f | 0 | 2015-04-22 8 | 1 | 0 | 4 | 38 | 18 | 28239 | 1326 | 455 | 6 | f | 0 | 2015-04-22 2 | 1 | 0 | 49 | 22 | 76 | 28239 | 2013 | 545 | 6 | f | 0 | 2015-04-22 the question straight forward, why query outputs no rows?
select filas.factura, filas.fila, filas.cantidad, retirados.cantidad, vendidos.cantidad, filas.estado articulos_factura_venta filas left join articulos_factura_venta retirados using (fila, color, talla, origen, factura, articulo, vendedor) left join articulos_factura_venta vendidos using (fila, color, talla, origen, factura, articulo, vendedor) join articulos on articulos.codigo = filas.articulo join tallas on tallas.codigo = filas.talla join colores on colores.codigo = filas.color join empleados on empleados.codigo = filas.vendedor filas.factura = 28239 , retirados.estado & 16 <> 0 , vendidos.estado & 8 <> 0 , filas.estado & 4 <> 0 order filas.estado i expect query subtract cantidad row has fila == 2 case estado & 16 <> 0 , hence expect 1 row fila == 2 , cantidad = 0
note: bit flags, not hardcoded, enum use in actual application written c++.
table definition
database# \d articulos_factura_venta column | type | modifiers ----------+------------------+-------------------------------------- fila | integer | not null cantidad | integer | not null color | integer | not null talla | integer | not null estado | integer | not null default 2 origen | integer | factura | integer | not null articulo | integer | not null precio | integer | not null vendedor | integer | anulado | boolean | default false iva | double precision | default 12.0 fecha | date | not null default ('now'::text)::date indexes: "articulos_factura_venta_pkey" primary key, btree (fila, factura, articulo, precio, talla, color, estado) "buscar_cantidad_venta_idx" btree (articulo, talla, color, origen) foreign-key constraints: "cantidades_venta_articulo_fkey" foreign key (articulo) references articulos(codigo) "cantidades_venta_color_fkey" foreign key (color) references colores(codigo) on update cascade on delete restrict "cantidades_venta_factura_fkey" foreign key (factura) references ventas(codigo) "cantidades_venta_origen_fkey" foreign key (origen) references compras(codigo) on update cascade on delete restrict "cantidades_venta_talla_fkey" foreign key (talla) references tallas(codigo) on update cascade on delete restrict "cantidades_venta_vendedor_fkey" foreign key (vendedor) references empleados(codigo) [1]the table contains thousands of rows, interested in these rows only, i.e. rows factura == 28239.
long story short, might work this:
select f.factura , f.fila , f.cantidad , r.cantidad , v.cantidad , f.estado articulos_factura_venta f -- join articulos on a.codigo = f.articulo -- noise -- join tallas t on t.codigo = f.talla -- join colores c on c.codigo = f.color join empleados e on e.codigo = f.vendedor left join articulos_factura_venta r on r.fila = f.fila , r.color = f.color , r.talla = f.talla , r.origen = f.origen , r.factura = f.factura , r.articulo = f.articulo , r.vendedor = f.vendedor , r.estado & 16 <> 0 left join articulos_factura_venta v on v.fila = f.fila , v.color = f.color , v.talla = f.talla , v.origen = f.origen , v.factura = f.factura , v.articulo = f.articulo , v.vendedor = f.vendedor , v.estado & 8 <> 0 f.factura = 28239 , f.estado & 4 <> 0 order f.estado; in particular these added where clauses voided attempted left join on respective tables , made act join:
and r.estado & 16 <> 0 , v.estado & 8 <> 0 one other sticky detail:
join empleados e on e.codigo = f.vendedor but f.vendedor can null. intention remove rows f.vendedor null result? because that's join does.
and commented 3 joins articulos, tallas , colores. fk columns not null, join nothing cost time , not using of columns.
table definition
a primary key constraint on 7 columns terrible idea. expensive , unwieldy. add surrogate primary key - suggest serial column:
you can still enforce uniqueness on set of 7 columns unique constraint - if need that.
unique , primary key constraints (per request in comment):
- how postgresql enforce unique constraint / type of index use?
- do need primary key table, has unique (composite 4-columns), 1 of can null?
- why can create table primary key on nullable column?
suggested table design:
create table articulos_factura_venta ( afv_id serial primary key -- pick column name fila integer not null, cantidad integer not null, color integer not null, talla integer not null, estado integer default 2 not null, factura integer not null, articulo integer not null, precio integer not null, fecha date not null default now()::date, origen integer, vendedor integer, anulado boolean default false, -- not null ? iva double precision default 12.0, constraint uni7 -- pick contraint name unique (fila, factura, articulo, precio, talla, color, estado) );then query can simplified to:
... left join articulos_factura_venta r on r.afv_id = f.afv_id , r.estado & 16 <> 0 left join articulos_factura_venta v on v.afv_id = f.afv_id , v.estado & 8 <> 0 ...