Complicated SQL statement for 3 tables
I have 3 different transaction tables that look very similar but are slightly different. This is due to the fact that there are 3 different types of transactions; depending on the types of transactions, the columns change, so to get them in 3NF I need to have them in separate tables (right?).
As an example:
t1: date, user, amount
t2: date, user, who, quantity
t3: date, user, what, quantity
Now I need a query that collects all transactions in each table for the same user, something like
select * from t1, t2, t3 where user = 'me'; (which of course doesn't work).
I have been looking into JOIN instructions but have not found a suitable way to do this. Thank.
EDIT: Actually, I want all columns from each table, not just the ones that are the same.
EDIT # 2: Yes, of course with a transactional type it won't break 3NF, so maybe my design is completely wrong. This is what really happens (this is an alternative currency system):
- Transactions between users, such as mutual loans. Thus, departments are swapped between users.
- Inventory is the physical material entered into the system; the user gets units for this.
- Consumption is a consumable physical material; the user has to pay for it.
| ------------------------------------------------- ------------------------- | | type | transactions | inventarizations | consumations | | ------------------------------------------------- ------------------------- | | columns | date | date | date | | | creditor (FK user) | creditor (FK user) | | | | debitor (FK user) | | debitor (FK user) | | | service (FK service) | | | | | | asset (FK asset) | asset (FK asset) | | | amount | amount | amount | | | | | price | | ------------------------------------------------- ------------------------- |
(Note that "sum" is in different units, these are records, and calculations are made on those sums. Outside the scope to explain why, but these are fields).
So the question changes to "Could / be this in one table or be multiple tables (as I have at the moment)? I need the previously described SQL statement to display the current balances."
(Should this now become a new question at all or is it OK to CHANGE?).
EDIT # 3: Since EDIT # 2 actually transforms this into a new question, I decided to post a new question as well . (I hope this is okay?).
source to share
You can specify default values as constants in select statements for columns where you have no data;
So
SELECT Date, User, Amount, 'NotApplicable' as Who, 'NotApplicable' as What from t1 where user = 'me'
UNION
SELECT Date, User, Amount, Who, 'NotApplicable' from t2 where user = 'me'
UNION
SELECT Date, User, Amount, 'NotApplicable', What from t3 where user = 'me'
which assumes what is Who and what are row type columns. You can also use Null, but you need some kind of placeholder.
I think that putting your additional information in a separate table and keeping all transactions in one table will work better for you, though unless there are other details that I missed.
source to share
I think the meat of your question is here:
depending on the types of transactions that change the columns, so to get them in 3NF, I need to have them in separate tables (right?).
I'm not a 3NF expert, but I'll get a little closer to your schema (which might cut your SQL down a bit).
It looks like your data items are: date , user , quantity , who and what . With this in mind, a more normalized schema might look something like this:
User ---- id, user info (username, etc) Who --- id, who info What ---- id, what info Transaction ----------- id, date, amount, user_id, who_id, what_id
The complexity of the foreign key constraint will depend on the database implementation, but this is a little clearer (and extensible).
source to share
What will be the outline of the outcome? - If you only want the minimum columns that are in all three tables, then it's easy, you just CONNECT the results:
SELECT Date, User, Amount from t1 where user = 'me'
UNION
SELECT Date, User, Amount from t2 where user = 'me'
UNION
SELECT Date, User, Amount from t3 where user = 'me'
source to share
Or you could "SubClass" them
Create Table Transaction
(
TransactionId Integer Primary Key Not Null,
TransactionDateTime dateTime Not Null,
TransactionType Integer Not Null,
-- Othe columns all transactions Share
)
Create Table Type1Transactions
{
TransactionId Integer PrimaryKey Not Null,
// Type 1 specific columns
}
ALTER TABLE Type1Transactions WITH CHECK ADD CONSTRAINT
[FK_Type1Transaction_Transaction] FOREIGN KEY([TransactionId])
REFERENCES [Transaction] ([TransactionId])
Repeat for other types of transactions ...
source to share