Here is the ceneria:
Database A: table A1:ID. table A2:ID, Description.(can be linked with ID)
Database B: table B2:ID, Description.(can be linked with ID)

Can I get the result like below:
    ID Description
   ------------------------------
    A1.ID A2.Description
    A1.ID ...
    A1.ID B2.Description
    A1.ID ...

Anyone can help? Thanks in advance!

Ally

I'd like to help u much but its not clear. now i'd say using 2 diffrent SELECT & use UNION to join them.
send me more, I might help more ;)

Always:),
Hovik Melkomian.

Any time you need to do a cross database join, you can usually reference the other database's tables like this:

Database1..Table

This is how you do it in MS-SQL at least. I don't know about other RDBMSs. An important consideration is that the DB user who's making this query has the appropriate rights on both databases. (And this only works where you have multiple databases on the same database server. Cross SERVER databases are a whole other situation.)

Peter
------------------------------------------------------
Work smarter, not harder.

First of all, I'd appreciate for your willing to help.
Here is more details:
Database A:
Table A1 Table A2
ID ID Description
1 1 book
2 2 paper
3 3 pen
4 5 knife
5
Database B:
Table B2
ID Description
3 blue pen
4 ruler
5 small knife

I'd like to get result in my crystal report like below:
1 book
2 paper
3 pen
3 blue pen
4 ruler
5 knife
5 small knife

Is that possible, how far i can do?

right now i simply add three tables together, table A1 links both A2 and B2,
but the report never get the description from table B2 in database B.

Thanks again!


Ally

I don't use crystal reports so I'm not sure what you need to do there, but as far as the database goes, it looks as though you need to merge a couple of different queries together. You can do so with a UNION.

SELECT t1.[ID], t1.Description FROM DatabaseA..TableA2 AS t1
    UNION ALL
SELECT t2.[ID], t2.Description FROM DatabaseB..TableB2 AS t2
ORDER BY [ID]

You can modify the SQL to add in any joins necessary.

Peter
------------------------------------------------------
Work smarter, not harder.

Ok my last guess was correct as planoie mentioned;)

Always:),
Hovik Melkomian.

Thank you for all your help.
But the problem is, in crystal report when i add tables from differenct database, it won't let me change the SQL query.
And it seems only can connect to one database at a time.
I just think crystal report should allow more than one database.
I have no idea, what need to do. Kind of frustrated.
Hope can get more help.
Thanks again.


Ally

Are you having problems with the SQL query in Crystal Reports itself? Please send an example.

Renee C. Walker

Let me add a bit of clarification to my previous posts.

When you need to do a cross database query (assuming that the databases are on the same server and you are using MSSQL) you only need to connect to one database. The query running against one database can call the other database internally.

As Renee asked, where exactly are you having the problem? The original post doesn't state whether the problem is with Crystal or just with the database query. If it's with Crystal, then I won't be of much further use as I don't use Crystal, but I certainly use lots of SQL.

Peter
------------------------------------------------------
Work smarter, not harder.

The task is to run a report in Crystal Report. Data is from two different database in one server.
Database A:
Table A1 Table A2
ID ID Description
1 1 book
2 2 paper
3 3 pen
4 5 knife
5
Database B:
Table B2
ID Description
3 blue pen
4 ruler
5 small knife

Table A1 in database A has all the IDs, but part of the description is in another datatbase table B2.
I'd like to get all the descriptions base on the id.

When i create the report in Crystal, i add all three tables from different database and link them with the id.
But when i run the report, it only get the data from one database at a time, either from table A2 or table B2, can't show both together.
I don't know how to get both, i tried to modify sql query using union, but crystal report seems won't let you change the sql query when you connect to different database.
Hope i explain it clear this time.
Thanks a lot for trying to help.

Ally