Selecting data from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting data from multiple tables

Author

Topic

Shynnie
Starting Member

4 Posts

Posted - 2012-02-13 : 06:26:14

Good day,

Found this forum and hope it might help me in resolving my problem.

I have 4 tables namely;

Rim
Accounts
History EFT
History ATM

Heres what im trying to do, i want to generate a statistical report (query) for the bank, based on Rim, Accounts and history.

A clients opens a profile and gets assigned a Rim number, than the client has multiple accounts under that rim, those accounts have multiple transactions from different accounts which are hosted by one rim.

I want to do a select from both tables and combine the data as follow.
So far i have gone up to retrieving data from rims and accounts, theoretically i know what i want but how to accomplish it has become a nightmare.

You re help will be much appreciated.

Tables

Rim table
Rim_no
----------
12345
54321

Account table
Rim_no Accounts
------------------
12345 8000123
12345 8000123
54321 8000321

history table
acount_no tran_name amt
------------------------------------
8000123 pay mario x
8000123 deposit x
8000321 pay mario x
8000321 deposit x

history table ATM
acount_no tran_name amt
------------------------------------
8000123 withdraw x
8000123 pos buy x
80012345 withdraw x
8000321 withdraw x
8000321 deposit x
8000321 pos buy x

Rim Account_no Transactins total_accounts Total_charges Total_transactions
------------------------------------------------------------------------------------------
12345 2 199.25 5
------------------------------------------------------------------------------------------
8000123 157.25 4
pay mario
withdraw
deposit
pos buy
------------------------------------------------------------------------------------------
80012345 42.00 1
withdraw
------------------------------------------------------------------------------------------
54321 1 53.45 4
------------------------------------------------------------------------------------------
8000321 53.45 4
pay mario
withdraw
deposit
pos buy
------------------------------------------------------------------------------------------

Carnalito

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 09:53:17

sorry not fully clear. can you post the output within code tags to make formatting correct?

also where did you get numeric values from? cant see it in any table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Shynnie
Starting Member

4 Posts

Posted - 2012-02-13 : 09:56:32

Hi,

Thanks, how do i attch a file on this forum?

Carnalito

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 10:13:18

you cant. host file in some shared servers and post the link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-02-13 : 14:26:46

Are you asking how to "join" tables in a single SQL statement?

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

Shynnie
Starting Member

4 Posts

Posted - 2012-02-14 : 01:20:46

Thanks guys, since I cant upload a file let me make this clear.

I have four tables namely:

Rim
Accounts
EFT_history
ATM_History

Rim has a unique rim_no, which is found is Accounts, accounts have many transactions either EFT or ATM.

I want to select a rim_no with all its associated accounts and all the transactions from EFT and ATM tables, in one set of results.

Example.
1 rim can have multiple accounts, those accounts can transact from EFT or ATM.

Now I want to do a select of a Rim with its accounts (all) and all transactions made from those accounts..

Rim 12345 (profile ID) has 5 accounts (either chk,sav,loan, personal loan), all those accounts have many transactions (eft payments, withdraws, monthly fees) which they been transaction for the month

Hope this clears up things

Regards,

Carnalito

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 10:06:24

sounds like this


SELECT columns...
FROM Rim r
INNER JOIN Accounts a
ON a.rim_no = r.rim_no
LEFT JOIN EFT e
ON e.AccntNo = a.AccntNo
LEFT JOIN ATM at
ON at.AccntNo = a.AccntNo

i've assumed EFT and ATM tables have AccntNo field so make sure you replace it with actual fields on which they're related

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-14 : 13:57:18

quote:
Originally posted by Shynnie

Thanks guys, since I cant upload a file let me make this clear.

Here are 2 links that will help you for this problem and in the future so you'll know how to post your DDL (schema), DML (insert statements) and Expected Output:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Shynnie
Starting Member

4 Posts

Posted - 2012-02-15 : 02:03:27

quote:
Originally posted by visakh16

sounds like this

SELECT columns...
FROM Rim r
INNER JOIN Accounts a
ON a.rim_no = r.rim_no
LEFT JOIN EFT e
ON e.AccntNo = a.AccntNo
LEFT JOIN ATM at
ON at.AccntNo = a.AccntNo

i've assumed EFT and ATM tables have AccntNo field so make sure you replace it with actual fields on which they're related

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi V, thanks this helps much.. yes both EFT and ATM have AcctNo field and now i got all transaction from an account either EFT or ATM.. thanks again.. i can write my report now..

Carnalito

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 09:41:48

welcome
glad that it sorted out the issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources