ReportServer Forum

richardmlaws

Member

Registered: 2016-06-29

Dynamic List of values from two different tables

Is it possible to display records from two different tables that are linked by a key field e.g

Table 1 - customers
*AccountNumber
CustomerName

Table 2 - orders
*CustomerNumber
OrderNumber

So I would basically like to display a list of orders (from table 2) that includes the CustomerNumber from table 2 and also the CustomerName from table 1 - the correct CustomerName should be pulled in by linking AccountNumber and CustomerNumber from both tables (the data field names are different on purpose for this example)

My data source is a MySQL database

Hope that makes sense

Thanks for any help or guidance

Thomas Davies

datenwerke

Registered: 2016-05-18

Re: Dynamic List of values from two different tables

Hi Richard,

welcome to the ReportServer forum.

If you are using a Dynamic List, then the easiest might be to work with a SQL join as the underlying query. For example, you could do a LEFT JOIN the customer
table to the orders table and use a query such as

SELECT O.col1, O.col2, ..., C.col1, C.col2, C.col3, ...  FROM orders O LEFT JOIN customers C ON (C.AccountNumber = O.CustomerNumber)

This would try for each of the data records in the orders table to find a matching customer record. If no customer record is found, then the order would still be in the result set but with null values on the customer attributes.

I hope this gives you a place to start.

Best Regards,
Thomas

richardmlaws

Member

Registered: 2016-06-29

Re: Dynamic List of values from two different tables

Thanks Thomas - I have managed to do this with my data now