How to connect to SharePoint for a LEFT JOIN sql from two LISTS?
I am using Visual Studio 2008, and I am able to query a SharePoint List successfully. How ever now I need to query two lists with a LEFT JOIN sql command. I was not able to find a way to do this using one connection string, so I used two connection strings (one for each list) and was able to put the results into "ds" an instance of the DataSet object. The DataSet object stores the data from each of the two lists, in two separate tables within "ds" as ds.Tables(0) and ds.Tables(1). I also create a DataRelation for parent and child columns.
At this point I am able to read the tables separately, by iteration through the object members.
My problem is I need to be able to retrieve the data using a single sql statement with a LEFT JOIN from both tables simultaneously. (I wish to avoid doing some kind of array iteration to avoid orphaning records)
Solutions I am looking for, either case would work:
- How do i set up a connection string to SharePoint, where I can read from two Lists for LEFT JOIN sql command?
- How do I set up a connection string to connect to a DataSet for a LEFT JOIN sql connection string.
For either one of these it would be helpful to know what the sql "FROM" statement might look like.
For example: "From LIST_A LEFT JOIN LIST_B ON (LIST_A.Field1 = LIST_B.Field1)"
I would sincerely appreciate the assistance, I am two weeks behind on the project, because it's been two weeks and I still can't figure it out.