Joining Tables in seperate databases
-
Sorry for writing this Question to this part, but i write it in SQL part, i got no answer 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 DebitsAndCredits 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 DebitsAndCredits Order BY AccountNumber i dont know why it is not working in my way. I expect a table has three columns (AccountNumber,Credit,Debit ) as a result. What is Wrong?What do u offer for me?
-
Sorry for writing this Question to this part, but i write it in SQL part, i got no answer 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 DebitsAndCredits 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 DebitsAndCredits Order BY AccountNumber i dont know why it is not working in my way. I expect a table has three columns (AccountNumber,Credit,Debit ) as a result. What is Wrong?What do u offer for me?
you must specify the database names in the query eg. Select ..... from MB0001..DebitsAndCredits m1 ...... Union Select ..... from MB0002..DebitsAndCredits m2 ...... If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850)
-
you must specify the database names in the query eg. Select ..... from MB0001..DebitsAndCredits m1 ...... Union Select ..... from MB0002..DebitsAndCredits m2 ...... If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850)
I have already done that. But i realized i forgot writing that when i asked