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

7 replies

You can specify default values ​​as constants in select statements for columns where you have no data;


SELECT Date, User, Amount, 'NotApplicable' as Who, 'NotApplicable' as What from t1 where user = 'me'
SELECT Date, User, Amount, Who, 'NotApplicable' from t2 where user = 'me'
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:

id, user info (username, etc)

id, who info

id, what info

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'
SELECT Date, User, Amount from t2 where user = 'me'
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
   [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
    select user from t2
    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




All Articles