Using a Join with a secondary data source

[Mike]

How can I use a Join to retrieve data into an InfoPath dropdown list
box?
I am using managed code, so I can build my own SQL query, but I can't
figure out how to set up the data source in InfoPath. I have tried all
sorts of data connections.

The Join that will get the data from SQL Server is:

select a.* , l.name_1, c.*
from COMPANIES c
left join client l on l.ID = c.COMPANY_CODE
left join address a on a.addr_id = l.addr_id
where
l.NAME_1 like 'john%'

But I can't figure out how to do that with InfoPath.

 

[S.Y.M. Wong-A-Ton]

This is a 2003 solution: Add a data connection to your form through the "Data
Connections" menu item and bind your drop-down to it. Retrieve the data
connection in code, modify its "Command" property by setting it to your SQL
statement, and call the "Query" method to refresh the data.

Note: To create joins in InfoPath, you need to use the "Shape" command. Add
more than one table to a data connection and then view the SQL statement
through the "Edit SQL" button to see how InfoPath builds the SHAPE for you.
You need to mimic this functionality to get your joins to work.