Link to home
Start Free TrialLog in
Avatar of RandyMFire
RandyMFire

asked on

Two Tables - Two Databases - How to Link

I have 2 databases one will call Database A, made up of the main report and the other Database B, which I'm using to get information from a sub-report.

I have  a database table in A with a Interger field lets say 1234567 is the output
I have a database table in B with a varchar fields lets say C1234567 is the output

Need to get info from table in database B into my report in SSRS, so I can use 2 fields.

I know i either have to strip the C from Database table B and bring it over to Database table A but not sure how?

Or do I create create or convert the field in database table A to string, then add the C then submit that as my parameter for the sub-report to link up table database field in B.

Not sure how to do either. Any help is appreciated. Also please send examples as I still consider a very basic beginner in all this. Thank you for your support.
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

If they all start with C, it's easy:
SELECT *
FROM DatabaseA..TableA t1
INNER JOIN DatabaseB..TableA t2
ON t1.MyField=REPLACE(t2.MyField,'C','')

If they all start with a single char, you can use:
ON t1.MyField=RIGHT(t2.MyField,LEN(t2.MyField)-1)

If they have any number of chars, you can use:
ON t2.MyField LIKE '%'+CAST(t1.MyField AS varchar)+'%'

Though this last one will be less efficient.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Then, the query is super-easy:


SELECT ...
FROM TableA a
INNER JOIN TableB b ON b.column_name_int = a.column_int
WHERE
    ...



Note that you still have the original combined column name available, it is just a computed column now, but it can be used just like any other column name.
Avatar of RandyMFire
RandyMFire

ASKER

Ok. This looks way over my head so I'm going to explain it in pictures.

See attached.
2012-11-09-095725.png
2012-11-09-095029.png
2012-11-09-093159.png
Your subreport will have to use the exact same parameter as your report. What you have to do is create the subreport query in a way that will strip the C from it. Like:
SELECT * FROM Table WHERE ID=REPLACE(@Param,'C','')

This way, you can use the same parameter to link the report to the subreport.
So see attached.
2012-11-09-110548.png