sql server programming: I have tow tables they are an invoice together there is a header and a
detail table I Have many more invoices in the detail table than I do in the
header. I want to delete the record in the detail that do not match any
invoice in the header. InvoiceID is the related field I have been banging my
head on this one any help would be most appreciated!!

Rick

CREATE TABLE [dbo].[InvoiceDetail] (
[Invoice ID] [int] NULL ,
[Item No] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Units of Measure] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Taxable] [bit] NULL ,
[Product Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Qty] [float] NULL ,
[Cost] [money] NULL ,
[Sell Price] [money] NULL ,
[Item Total] [money] NULL ,
[GL Account] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VendorID] [int] NULL ,
[ListPrice] [money] NULL ,
[Line Sequence] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PricingCost] [money] NULL ,
[FulfillmentCost] [money] NULL ,
[ContractID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PriceSource] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvoiceCustomerProductID] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ContractFlag] [bit] NOT NULL ,
[ProductClassID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PriceClassID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderLineSequence] [int] NULL
) ON [PRIMARY]




CREATE TABLE [dbo].[InvoiceHeader] (
[Invoice ID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Invoice Date] [datetime] NULL ,
[Due Date] [datetime] NULL ,
[PO Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tax Group ID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Customer ID] [int] NULL ,
[Bill To] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Billing To Customer Name] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Billing Address 1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Billing Address 2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Billing to City] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Billing to State] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Billing to Zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Billing to Country] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Ship Date] [datetime] NULL ,
[Ship to ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ship to Customer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Ship Address 1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Ship Address 2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Ship City] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ship State] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ship Zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ship Country] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sales Person ID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Terms] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Payment Method] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Subtotal] [money] NULL ,
[Discount Percent] [float] NULL ,
[Discount Amount] [money] NULL ,
[Tax Percent] [money] NULL ,
[Tax Amount] [money] NULL ,
[Freight Amount] [money] NULL ,
[Total] [money] NULL ,
[Amount Paid] [money] NULL ,
[Balance Due] [money] NULL ,
[Taxable Subtotal] [money] NULL ,
[SpecialInstructions] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OE Notes] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DealerPOID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Summary Invoice Number] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CostCenterID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL ,
[RouteCodeID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSourceID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReturnReasonCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Order Taker ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipToPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

[quoted text, click to view]

Sorry I figured it out. Thanks anyway!!!

delete dbo.InvoiceDetail
FROM dbo.InvoiceDetail LEFT JOIN dbo.InvoiceHeader ON
dbo.InvoiceDetail.[Invoice ID] = dbo.InvoiceHeader.[Invoice ID]
WHERE (((dbo.InvoiceHeader.[Invoice ID]) Is Null))

DELETE FROM InvoiceDetail
WHERE NOT EXISTS
(SELECT *
FROM InvoiceHeader
WHERE [Invoice ID] = InvoiceDetail.[Invoice ID])

Presumably you will now declare the foreign key on the Detail table to
prevent this problem from happening again.

--
David Portas
SQL Server MVP
--

On second look you appear to have much more serious problems. Nullable
columns, no keys and lots of transitive dependencies. I suggest you need to
review and redesign your schema from scratch.

--
David Portas
SQL Server MVP
--