sql queries - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

sql queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-02 : 07:29:31

hoshang siwaily writes "hi, i have two queries and would like to right join them 0n (TblAccountDetails.bank_id = query 2 (bank_name).

please help

many thanks

Query 1

SELECT Bab_Name,tblbanks.Bank_Name,TblAccountDetails.bank_id,count(account_No)as total_accounts, currency_type
FROM TblAccountDetails
INNER JOIN tblbabs on TblAccountDetails.Bab_id = tblbabs.Babid
INNER JOIN tblbanks on tblaccountdetails.bank_id = tblbanks.bankid
INNER JOIN tblaccounttype on TblAccountDetails.Accounttype = tblaccounttype.id
INNER JOIN tblcurrencytype on tblaccountdetails.currency_type = tblcurrencytype.id
WHERE bab_id = 2
Group by Bab_Name,tblbanks.Bank_Name,bank_id,currency_type
ORDER BY Bab_Name

Query 2

select bank_name,sum(paid_in_amount) as paidin, sum(paid_out_amount) as paidout
from tbltran
group by bank_name"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-02 : 15:53:53

When You have several tables in your queries ALWAYS prefix the column names to avoid AMBIGUITY.
Good ALIASING of the table names also helps to make the queries easier to read...

SELECT
	Bab_Name
	,tblbanks.Bank_Name
	,TblAccountDetails.bank_id
	,count(account_No)as total_accounts
	,currency_type
	,Query2.bank_name
	,Query2.paidin
	,Query2.paidout
FROM
	(	SELECT
			bank_name
			,sum(paid_in_amount) as paidin
			,sum(paid_out_amount) as paidout
		FROM
			tbltran
		GROUP BY
			bank_name ) AS Query2
	LEFT JOIN
	(
		TblAccountDetails tad
		INNER JOIN tblbabs on tad.Bab_id = tblbabs.Babid
		INNER JOIN tblbanks on tad.bank_id = tblbanks.bankid
		INNER JOIN tblaccounttype on tad.Accounttype = tblaccounttype.id
		INNER JOIN tblcurrencytype on tad.currency_type = tblcurrencytype.id
	)
	ON Query2.bank_name = tad.bank_id
WHERE
	bab_id = 2
GROUP BY
	Bab_Name,tblbanks.Bank_Name,bank_id,currency_type
ORDER BY
	Bab_Name

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources