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.
| Author |
Topic |
|
o9z
Starting Member
23 Posts |
 Posted - 2011-02-04 : 10:41:45
|
| How would I join 3 databases with identical table names and field names? Here is the query for what I want returned for the "FEC" database:SELECT ActDescr, Actnumbr_1, Actnumbr_2, Actnumbr_3, Actnumbr_4 AS AcctNumb FROM GL00100 WHERE ACTNUMBR_1 BETWEEN '0' AND '9' AND ACTNUMBR_3 BETWEEN '50000' AND '59999'Now I want to join the FECS database and MGT database to return the results from all 3. Once All data is there, I will use a SQL Report to filter by database(which is also company name). So I guess I would need to add a field called Company Name which is equal to the database name that each record comes from. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-02-04 : 11:39:53
|
| investigateselect * from (SELECT ActDescr, Actnumbr_1, Actnumbr_2, Actnumbr_3, Actnumbr_4 AS AcctNumb FROM server.database1.dboGL00100 WHERE ACTNUMBR_1 BETWEEN '0' AND '9' AND ACTNUMBR_3 BETWEEN '50000' AND '59999'union allSELECT ActDescr, Actnumbr_1, Actnumbr_2, Actnumbr_3, Actnumbr_4 AS AcctNumb FROM server.database2.dboGL00100 WHERE ACTNUMBR_1 BETWEEN '0' AND '9' AND ACTNUMBR_3 BETWEEN '50000' AND '59999'union allSELECT ActDescr, Actnumbr_1, Actnumbr_2, Actnumbr_3, Actnumbr_4 AS AcctNumb FROM server.database3.dboGL00100 WHERE ACTNUMBR_1 BETWEEN '0' AND '9' AND ACTNUMBR_3 BETWEEN '50000' AND '59999') a |
 |
|
|
o9z
Starting Member
23 Posts |
Posted - 2011-02-04 : 11:51:01
|
| THanks! That worked. Is there a way to add another field that contains the database name of each record? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-02-07 : 05:00:37
|
| Yes add it in each select statementMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
How to join 3 dbs with same table/field names?