Need help in establishing table relationships
-
Hello everyone. I have 3 table in my database among which I'm trying to establish relationships. I've been able to establish a relationship between 2 of them. The Customer table which has all the clients which appear only once. The primary key for this table is the Client Number field. I also have a Volume table that contains all the volumes keyed for each customer. As you would expect the same customer will appear numerous times in this table. I established a one-to-many relationship between these two tables connecting the prmary key of the Customer Table and the BoxNumber field of the volume table.
Looking at the Transaction code table, you'll notice that a single customer may have serveral codes that apply to them. Everything works well until I try to establish a relationship between the Customer table and the Transaction code table. At that point things begin to double. Why is this happening? And can I fix it so that I can use all 3 tables?
Customer Table
Client Name Client Number (primary key) 96001 / 70840 - Wachovia Mortgage Corp. 1 96011 Clayton (Vanderbilt) Mortgage 11 96012 / 70829 - Home Equity 12 96013 - World Omni 13 96014 - World Omni 14 96019 - ALLTEL Communications - Cellware 19 96002 - Vehicle Leasing 2 96020 - Vehicle Leasing 20 96022 - Worldcom 22 70884 - Capital One Bank 23 96024 - World Omni Finance 24 70881 - Virginia Natural Gas 248 70885 - Capital One FSB 25 96026 / 70835 - BTI Corp. 26 96028 - Sprint SubDoc 28 ---------------------------------------------------------------------
Volume Table
Shift SiteID Box Number Foreign Key
Image Items 1st "CH" 1 August 2004 8/10/2004 209 1st "CH" 11 August 2004 8/27/2004 23 1st "CH" 12 January 2006 1/10/2006 2 1st "CH" 19 April 2005 4/29/2005 19604 1st "CH" 23 August 2005 8/26/2005 53880 1st "CH" 248 December 2004 12/14/2004 1512 1st "CH" 25 February 2006 2/17/2006 9384 1st "CH" 26 May 2005 5/4/2005 409 1st "CH" 28 January 2006 1/9/2006 0 1st "CH" 3 February 2006 2/24/2006 34 1st "CH" 31 July 2004 7/21/2004 227 1st "CH" 375 January 2005 1/31/2005 706 1st "CH" 40 December 2005 12/1/2005 7595 1st "CH" 487 March 2005 3/8/2005 191 ------------------------------------------------------------------------------
Transaction Code Table
BoxNumber Foreign Key
Transacation Code Definitions Volume 26 1901 Lockbox Rental 0 26 1900 Monthly Maintenace 0 26 1908 Multiple Mailout 0 26 1910 Special Audits Mail Study 0 26 1911 Special Logs 0 26 1912 Interoffice Mail Per Box 0 26 1914 Courier Charges 0 26 1915 Data Transmission 0 26 1917 Stop File 0 -
When you say "everything begins to double," I'm guessing you mean in a SELECT query involving all three tables. Correct? And, because you mentioned doubling, I'm going to go out on a limb and guess that there are two records in the volume table for Customer 26.
Now, when I look at your sample data, I see a Volume column in the Transaction Code Table. Is that intended to be related to a row in the Volume Table? It's hard to tell, as you didn't list a primary key for the Volume Table (you do have a primary key on each table, don't you?).
However, if you write your query as you described, it would likely look like:
SELECT Customer.*, Volume.*, Transaction.*
FROM Customer
JOIN Volume ON Customer.ClientNumber = Volume.BoxNumber
JOIN Transaction ON Customer.ClientNumber = Transaction.BoxNumber
This will essentially give you a cross-product of the Volume and Transaction tables, limited only where they share a Box Number. Why? Simply because there's no relationship between the Volume and Transaction tables. If you have two Volumes, and no way to tell which Transaction goes with which Volume, it will just give you all the Transactions on both Volumes. It's the only way SQL has to deal with that scenario.
So, based on the sample data, I would suggest creating a relationship between Transaction and Volume, and having that inherit the relationship between Volume and Customer.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply