I am relatively fresh to SQL's query language and I need a reminder of what I am doing wrong. I realize in advance that my DB structure is not optimal so any tips you might think of while reading my question is greatly appreciated.
Here is what I am trying to do:
I have four different tables, all four containing record information of previous transactions. The tables have three relevant columns, amount, date and UserID. I want to select Amount and Date from all four columns so that the output will be four rows, each row holding the amount and date from one of the tables. Here is how (dates are saved as long values):
Table 1
Row Amount Date UserID
1 1000 0 1
2 2000 2674800000 1
3 3000 5094000000 1
Table 2
Row Amount Date UserID
1 1000 0 1
2 2000 2674800000 1
3 3000 5094000000 1
The output I want
Row Amount Date UserID
1 1000 0 1
2 1000 0 1
3 2000 2674800000 1
4 2000 2674800000 1
5 3000 5094000000 1
6 3000 5094000000 1
The above is how I want my data in the new view to look, but all I get is three entries per amount (so, three entries for 1000, 2000 and 3000)... And if I add another table into the mix, it seems to increase exponentially.
Here is my current SQL query:
SELECT T.Amount, T.Date FROM (SELECT Amount, Date, UserID FROM LoanRecords) AS T JOIN (SELECT Amount, Date, UserID FROM ExpenseRecords) AS E ON T.UserID = E.UserID
Also, if someone could give me some pointers on how to get this to also show the SUM() total for each date, that would be great.
You would use union all
:
SELECT T.Amount, T.Date, T.UserID
FROM ((SELECT Amount, Date, UserID FROM LoanRecords
) UNION ALL
(SELECT Amount, Date, UserID FROM ExpenseRecords
)
) t;
Actually, the subquery isn't needed:
SELECT Amount, Date, UserID FROM LoanRecords
UNION ALL
SELECT Amount, Date, UserID FROM ExpenseRecords
ORDER BY UserID, Date;
I added the ORDER BY
because that seems to be part of the intention of the query.
You can add additional tables using UNION ALL
in either query.