2 tables with similar data, possibly dupes...

[Jesterhoz]

Hello All,

I have two tables that contain invoice data. Sales table has fields:

InvNo
InvDate
CustCode
InvAmt
RouteNum

Invoice table has:

InvNo
InvDate
CustCode
InvAmt
Route (notice different field name from first table!!!)

There are multiple records for each invoice number and sometimes an invoice
is in both tables. I want to query the main table, Sales, and also check in
the Invoice table to look for an invoice. However, I don't want any
particular occurence of an invoice duplicated. Any ideas?

Any help is much appreciated.

Thanks,

Trevor

 

[Chris2]

Hello All,

I have two tables that contain invoice data. Sales table has fields:

InvNo
InvDate
CustCode
InvAmt
RouteNum

Invoice table has:

InvNo
InvDate
CustCode
InvAmt
Route (notice different field name from first table!!!)

There are multiple records for each invoice number and sometimes an invoice
is in both tables. I want to query the main table, Sales, and also check in
the Invoice table to look for an invoice. However, I don't want any
particular occurence of an invoice duplicated. Any ideas?

Any help is much appreciated.

Thanks,

Trevor

Trevor,

Why are the Table so similar (it seems a major violation of First
Normal Form (1NF))?

(I get the nagging field you're working with a DB converted out of a
flat-file driven application.)

Ok, I have to make assumptions here:

CREATE TABLE Invoice
(InvNo LONG
,InvDate DATETIME
,CustCode LONG
,InvAmt CURRENCY
,Route LONG
,CONSTRAINT pk_Invoice
PRIMARY KEY (InvNo
,InvDate
,CustCode
,InvAmt
,Route)
)

I did the DDL that way because, from the information supplied, the
only way a PK could tell the difference between the two tables is if
it spanned all five columns, with RouteNum and Route making the
difference.

No, but wait, it's stated there can be duplicates in the Invoice
table, and the DDL above would prevent it.

Uh . . . time to fall back on the standby:


Please provide the DDL (including CONSTRAINTS)
for all of the Tables involved. If the DDL is
not available, please include a well-formatted
text description (monospace-font) of the
relevant portions of the structures of each
Table, including a description of the Primary
and Foreign Keys (i.e. "relationships").
Please also include some sample data from each
(enough rows from each table to allow any
needed Queries to be executed). Please also
include the expected output.


Sincerely,

Chris O.




Sincerely,

Chris O.


SEL