Thank you very much for the design help... this is just what I am looking for, just wasn't clear how to best do it :)
BTW, you can use a case statement to help with signing the number properly. I just discovered this the other day and am really tickled with it!
I am not very experienced with SQL, could you show an example?
Thanks very much, Charles
On Mar 2, 2004, at 1:24 PM, Boyd E. Hemphill wrote:
Charles:
I am not quite sure by what you mean, " How do I get it to show me as I like, one in and one out per line, rather then an in for every out and vice-versa?"
It seems for your example below you want a result set of 4 rows
I don't think your design is appropriate, however your suggestion at the
end is on the right track.
Tbl_users is good.
Now use these: Tbl_transaction Trans_Id | user | trans_type_id | trans_amt | entry_dtm ---------+------+----------------+-----------+---------- 1 | 1 | 1 | 20.00 | timestamp 2 | 1 | 2 | 100.00 | timestamp 3 | 1 | 2 | 50.00 | timestamp 4 | 1 | 1 | 40.00 | timestamp
Tbl_transaction_type Trans_type_id | trans_nm | trans_cd --------------+----------+--------- 1 | Debit | D 2 | Credit | C
This provides you with a foundation for flexibility if you wish to add
more transaction types in the future such as a transfer or stock option.
BTW, you can use a case statement to help with signing the number properly. I just discovered this the other day and am really tickled with it!
Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc.
Life is not a journey to the grave arriving safely in a well preserved
body, but rather a skid in broadside, thoroughly used, totally worn, and
loudly proclaiming: "WOW! What a ride!"
-----Original Message----- From: charles kline [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 10:50 AM To: [EMAIL PROTECTED] Subject: Need help with ledger type query
Hi all,
I have 3 tables:
tbl_users
id | user ----------------------- 1 | charles
tbl_ins
id | in |date |user ____________________________________ 1 | 100.00 |timestamp |1 2 | 50.00 |timestamp |1
tbl_outs
id | out |date |user ____________________________________ 1 | 20.00 |timestamp |1 2 | 40.00 |timestamp |1
I want this to work like a checkbook register, where by date, I display the ins and outs (one per line) with a running balance.
Here is my query (I know it does not have the balance, but I can work that part out).
SELECT tbl_users.id, tbl_ins.in, tbl_outs.out FROM tbl_users LEFT JOIN tbl_ins ON tbl_ins.user = tbl_users.id LEFT JOIN tbl_outs ON tbl_outs.user = tbl_users.id WHERE tbl_users.id = 1
How do I get it to show me as I like, one in and one out per line, rather then an in for every out and vice-versa?
My other thought was to have one table with positive and negative values in an AMOUNT field...
Thanks, Charles
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
