Postgres Pro
Downloads
Home   >   mailing lists

Thread: Find records that do not contain an item

Find records that do not contain an item

From
Keith Worthington
Date:
Hi All,

I have rather peculiar query to work out when I get back to work
tomorrow and I am hoping that one of you can point me off in the correct
direction.

I have two tables.

DROP TABLE sales_order.tbl_line_item;
CREATE TABLE sales_order.tbl_line_item
(
   so_number int4 NOT NULL,
   so_line int2 NOT NULL,
   quantity float4 NOT NULL DEFAULT 0,
   item_id varchar(20) NOT NULL,
   unit_price numeric,
   po_number int4,
   reviewed bool NOT NULL DEFAULT false,
   audit_user varchar(64) NOT NULL DEFAULT 'default'::character varying,
   audit_timestamp timestamp NOT NULL DEFAULT
('now'::text)::timestamp(6) with time zone,
   CONSTRAINT tbl_line_item_pkey PRIMARY KEY (so_number, so_line),
)
WITHOUT OIDS;

13573,6,1,PSNC,58.00,,f,default,2005-05-13 14:02:50.798894
13595,3,4,PSNC,325.00,,f,default,2005-05-13 14:02:50.798894
13727,2,1,PSNA1020,57.00,,f,default,2005-05-13 14:02:50.798894
13709,1,4,PSNC,238.00,,f,default,2005-05-13 14:02:50.798894

DROP TABLE sales_order.tbl_item_bom;
CREATE TABLE sales_order.tbl_item_bom
(
   so_number int4 NOT NULL,
   so_line int2 NOT NULL,
   so_subline serial NOT NULL,
   quantity float4 NOT NULL,
   item_id varchar(20) NOT NULL,
   component_type varchar(6) NOT NULL DEFAULT 'other'::character varying,
   CONSTRAINT tbl_net_item_pkey PRIMARY KEY (so_number, so_line,
so_subline),
)
WITHOUT OIDS;

13573,6,3345,50,120-089-06-15,net
13573,6,3346,30,RHT3S050BK,other
13573,6,3347,9,SH120ZP,other
13573,6,3348,4,TMPSN,other
13595,3,3551,50,120-089-06-15,net
13595,3,3552,30,RHT3S050BK,other
13595,3,3553,9,SH120ZP,other
13709,1,3609,150,120-089-06-25,net
13709,1,3610,63,RHT3S050BK,other
13709,1,3611,16,SH120ZP,other
13709,1,3612,4,TPMSN,other

I am trying to find the sales orders containing a 'PSNC' that does NOT
contain a 'TMPSN'.

Given the data above I would like to return 13595.

--
Kind Regards,
Keith

Re: Find records that do not contain an item

From
Christoph Della Valle
Date:
hi

you can try something like this:

select *
from sales_order.tbl_line_item
where sales_order.tbl_line_item.item_id='PSNC' and
      sales_order.tbl_line_item.so_number not in (
                    select sales_order.tbl_item_bom.so_number
                    from sales_order.tbl_item_bom
                    where sales_order.tbl_item_bom.item_id != 'TMPSN');

hope it helps,
christoph

Keith Worthington schrieb:
> Hi All,
>
> I have rather peculiar query to work out when I get back to work
> tomorrow and I am hoping that one of you can point me off in the correct
> direction.
>
> I have two tables.
>
> DROP TABLE sales_order.tbl_line_item;
> CREATE TABLE sales_order.tbl_line_item
> (
>   so_number int4 NOT NULL,
>   so_line int2 NOT NULL,
>   quantity float4 NOT NULL DEFAULT 0,
>   item_id varchar(20) NOT NULL,
>   unit_price numeric,
>   po_number int4,
>   reviewed bool NOT NULL DEFAULT false,
>   audit_user varchar(64) NOT NULL DEFAULT 'default'::character varying,
>   audit_timestamp timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6)
> with time zone,
>   CONSTRAINT tbl_line_item_pkey PRIMARY KEY (so_number, so_line),
> )
> WITHOUT OIDS;
>
> 13573,6,1,PSNC,58.00,,f,default,2005-05-13 14:02:50.798894
> 13595,3,4,PSNC,325.00,,f,default,2005-05-13 14:02:50.798894
> 13727,2,1,PSNA1020,57.00,,f,default,2005-05-13 14:02:50.798894
> 13709,1,4,PSNC,238.00,,f,default,2005-05-13 14:02:50.798894
>
> DROP TABLE sales_order.tbl_item_bom;
> CREATE TABLE sales_order.tbl_item_bom
> (
>   so_number int4 NOT NULL,
>   so_line int2 NOT NULL,
>   so_subline serial NOT NULL,
>   quantity float4 NOT NULL,
>   item_id varchar(20) NOT NULL,
>   component_type varchar(6) NOT NULL DEFAULT 'other'::character varying,
>   CONSTRAINT tbl_net_item_pkey PRIMARY KEY (so_number, so_line,
> so_subline),
> )
> WITHOUT OIDS;
>
> 13573,6,3345,50,120-089-06-15,net
> 13573,6,3346,30,RHT3S050BK,other
> 13573,6,3347,9,SH120ZP,other
> 13573,6,3348,4,TMPSN,other
> 13595,3,3551,50,120-089-06-15,net
> 13595,3,3552,30,RHT3S050BK,other
> 13595,3,3553,9,SH120ZP,other
> 13709,1,3609,150,120-089-06-25,net
> 13709,1,3610,63,RHT3S050BK,other
> 13709,1,3611,16,SH120ZP,other
> 13709,1,3612,4,TPMSN,other
>
> I am trying to find the sales orders containing a 'PSNC' that does NOT
> contain a 'TMPSN'.
>
> Given the data above I would like to return 13595.
>