Re: Need help with ledger type query
If you are wanting a checkbook like display then each entry is either a debit or credit and each is on its own line. Your query will display this. I would add an ifnull to display a zero when the the debit/credit is null. Original Message On 3/2/04, 10:50:15 AM, charles kline [EMAIL PROTECTED] wrote regarding 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]
RE: Need help with ledger type query
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]
Re: Need help with ledger type query
Boyd, 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]
