General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
How do you get all records from three tables
How do you get all possible combinations from three tables into a fourth table - but only if that particular comination does not already exist?
The kind of data I am working with is:-
Sample report from a Budget table:
Accounts: Periods: Categories: Budget amount:
---------------------------------------------------------------------
Rent Jan 2004 South 100.00
Rent Feb 2004 South 200.00
Electricity Jan 2004 South 300.00
The records in the budget table look like this:
CID FK_Accounts FK_Periods FK_Categories nAmount
--------------------------------------------------------------
B1 A1 P1 C2 100.00
B2 A1 P2 C2 200.00
B3 A2 P1 C2 300.00
The three FK (foreign key) fields come from three tables as follows:
a) Accounts:
CID Description
-------------------
A1 Rent
A2 Electricity
b) Periods:
CID Description
-------------------
P1 Jan 2004
P2 Feb 2004
c) Categories:
CID Description
-------------------
C1 North
C2 South
Here is the question: I want to create a record in Budget for every possible combination of the records in the three tables, but only if that record does not exist already in Budget. Only one of each combination is allowed in Budget.
Typical number of records:
a) Accounts: 300
b) Periods: 24
c) Categories: 100
The budget table is therfeore quite big.
Is there a good SQL approach to this kind of problem?
Cyril
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only