Can I connect a result of SQL query to external database in SharePoint list?
As I understand - External Content types could map only one table for list, but I need to get some combined data from few tables, not single one. So I am trying to get result with "Content Source" in SPD, it allows queries as I understand.
By example I have external database with tables 'CountryRegion' and 'SomeTable', screenshots are below:
I make some logic in query and get result, by example
SELECT * FROM AdventureWorks2008.Person.CountryRegion R INNER JOIN AdventureWorks2008.dbo.SomeTable T ON R.CountryRegionCode = T.CountryRegionCode
I get this result:
Now I created "Content Source" in SharePoint Designer, it allowed me to make this select query:
But how can I map this result to SharePoint list?