JOIN tables in different databases
-
I have n databases, each one has the same tables,Sure same tables in different databases has different values. I want to join them in a just one table in dataset. For example, i have 2 databases MB0001 and MB0002 , also a table named IntegratedChequeAccounts in two . I want to join two to one. SELECT * FROM mb0001.DBO.IntegratedChequeAccounts , Mb0002.DBO.IntegratedChequeAccounts In this sample IntegratedChequeAccounts tables has 3 columns and each of them has 2 row. after running "select", i get 6columnsX4row as a table. But i desire 3columnsX4rows. Any Idea to successed in.
-
I have n databases, each one has the same tables,Sure same tables in different databases has different values. I want to join them in a just one table in dataset. For example, i have 2 databases MB0001 and MB0002 , also a table named IntegratedChequeAccounts in two . I want to join two to one. SELECT * FROM mb0001.DBO.IntegratedChequeAccounts , Mb0002.DBO.IntegratedChequeAccounts In this sample IntegratedChequeAccounts tables has 3 columns and each of them has 2 row. after running "select", i get 6columnsX4row as a table. But i desire 3columnsX4rows. Any Idea to successed in.
You need to UNION the results into one dataset. What you are doing here is putting the rows side-by-side by joining the result set of one with the result set of another.
SELECT column1, column2, column3
FROM mb0001..IntegratedCheckAccounts
UNION
SELECT column1, column2, column3
FROM mb0002..IntegratedCheckAccounts
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
-
You need to UNION the results into one dataset. What you are doing here is putting the rows side-by-side by joining the result set of one with the result set of another.
SELECT column1, column2, column3
FROM mb0001..IntegratedCheckAccounts
UNION
SELECT column1, column2, column3
FROM mb0002..IntegratedCheckAccounts
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Thanks for answer but i dont know why it is not working in my way. I have a table named "DebitsAndCredits" which has fallowing columns AccountNumber char(12) DebitCredit bit Amount money I have the same table in two databases which are MB0001 and MB0002 I would like to join them in a one result set.I write SELECT DISTINCT (AccountNumber ) , (SELECT SUM(Amount ) FROM DebitsAndCredits m WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,, (SELECT SUM(Amount ) FROM DebitsAndCredits m WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit FROM DebitsAndCreditsf Order BY AccountNumber UNION SELECT DISTINCT (AccountNumber ) , (SELECT SUM(Amount ) FROM DebitsAndCredits m WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit, (SELECT SUM(Amount ) FROM DebitsAndCredits m WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit FROM DebitsAndCreditsf Order BY AccountNumber What do u offer for me?
-
Thanks for answer but i dont know why it is not working in my way. I have a table named "DebitsAndCredits" which has fallowing columns AccountNumber char(12) DebitCredit bit Amount money I have the same table in two databases which are MB0001 and MB0002 I would like to join them in a one result set.I write SELECT DISTINCT (AccountNumber ) , (SELECT SUM(Amount ) FROM DebitsAndCredits m WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,, (SELECT SUM(Amount ) FROM DebitsAndCredits m WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit FROM DebitsAndCreditsf Order BY AccountNumber UNION SELECT DISTINCT (AccountNumber ) , (SELECT SUM(Amount ) FROM DebitsAndCredits m WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit, (SELECT SUM(Amount ) FROM DebitsAndCredits m WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit FROM DebitsAndCreditsf Order BY AccountNumber What do u offer for me?
Try this:
SELECT AccountNumber,
SUM(CASE DebitCredit=0 THEN Amount ELSE 0 END) as Credit,
SUM(CASE DebitCredit=1 THEN Amount ELSE 0 END) as Debit
FROM MB0001..DebitsAndCredits
ORDER BY AccountNumber
GROUP BY AccountNumber
UNION
SELECT AccountNumber,
SUM(CASE DebitCredit=0 THEN Amount ELSE 0 END) as Credit,
SUM(CASE DebitCredit=1 THEN Amount ELSE 0 END) as Debit
FROM MB0002..DebitsAndCredits
GROUP BY AccountNumber
ORDER BY AccountNumber
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums