Reputation: 3442

Getting Value From Another Table with a Foreign Key

I've built a report with the report wizard in C#, and I can see my report in ReportViewer. My problem is that I have 2 tables that are related. The value of a column in the first table is a foreign key on the other table. All I see in that column is just the foreign key. I'd like to see the corresponding value from the other table.

How can I see value of the column from the second table?

Upvotes: 0

Answers (5)

KM.

Reputation: 103587

You should just join on the foreign key columns:

SELECT
    a.*,b.YourNeededColumnHere
    FROM TableA            a
        INNER JOIN TableB  b ON a.columnX=b.columnX

However, if you are having problems doing this in reporting services, just create a view:

CREATE VIEW CombinedAB
AS 

SELECT
    a.*,b.YourNeededColumnHere
    FROM TableA            a
        INNER JOIN TableB  b ON a.columnX=b.columnX

GO

you should now be able to run your report off the CombinedAB view as:

SELECT
    * 
    FROM CombinedAB
    WHERE ...your conditions here...

Upvotes: 4

Rob Farley

Reputation: 15849

Change your query to one that involves the fields from the appropriate tables.

SELECT t1.*, t2.value
FROM table1 t1 
   JOIN table2 t2 ON t1.t2id = t2.id

But if you can't, because the data sources are separate, then you'll be wanting another option.

Assuming you have two datasets, from separate data sources.

Put a table in your report to show information from DataSet1 (or whatever it's called). Then put a rectangle in place of one of your textboxes, and then put a table in there, which you attach to DataSet2. Then put a filter on this table, so that it only shows records from DataSet2 which correspond to the appropriate value in DataSet1.

Or, wait for SQL Server 2008 R2 (currently in CTP), which provides a Lookup function for exactly this purpose.

Rob

Upvotes: 1