From: | Keith Worthington <KeithW(at)NarrowPathInc(dot)com> |
---|---|
To: | PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Find records that do not contain an item |
Date: | 2006-06-16 05:57:20 |
Message-ID: | 44924840.7020900@NarrowPathInc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Della Valle | 2006-06-16 07:06:00 | Re: Find records that do not contain an item |
Previous Message | John DeSoi | 2006-06-16 00:14:13 | Re: windows update screwed up PostgreSQL |