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?).
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.
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).
You should consider STI architecture (unidirectional inheritance). That is, put all the different columns in the same table and put them under the same index.
Alternatively, you can add indexes to other columns you do.
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'
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 ...
How can I just leave unneeded columns null and add a TransactionType column? This will result in a simple SELECT statement.
select *
from (
select user from t1
union
select user from t2
union
select user from t3
) u
left outer join t1 on u.user=t1.user
left outer join t2 on u.user=t2.user
left outer join t3 on u.user=t3.user