You can see the CRUD operations using REST here. In this article, we are going to discuss how to join two lists in SharePoint 2013 using REST.

I have two lists in the Office 365 SharePoint site.

  1. ListA
  2. ListB

ListA contains two columns -> Title and Code.

ListB contains four columns including Lookup column -> Title, EmployeeName, Salary, Dept (looks up to ListA).

Find the below screenshots of ListA and ListB.

SharePoint 2013

SharePoint 2013

Now, I want to join these two Lists and query the ListB.

The REST query will looks like the below.

https://server/Sites/SiteName/__api/web/lists/GetByTitle('ListB')/items?$Select=ListBField1,ListBField2 LookUpColumnInListB/ListAField,LookUpColumnInListB/ListAFiled&$expand=LookUpColumn

In our example, the REST query looks like the following after these setting values.

https://servername/Sites/SiteName/__api/web/lists/GetByTitle('ListB')/items?$Select=Title,EmployeeName,Salary,Dept/Title,Dept/Code&$expand=Dept

SharePoint 2013

SharePoint 2013

SharePoint 2013

Once you create the project, you will find default pages and scripts under Project Solution.

SharePoint 2013

On the default.aspx page, we will make some changes to display the data.

Write the below HTML code for User Interface under ContentPlaceHolderID tag.

  1. <table>
  2. <tr>
  3. <td>
  5. <table id="tblEmployees" class="mytable">
  6. </table>
  8. </td>
  9. </tr>
  10. </table>

SharePoint 2013

Method name - GetEmployeeDetails()

Call this method in Document.ready function.

  1. $(document).ready(function () {
  2. GetEmployeeDetails();
  3. });
  5. function GetEmployeeDetails() {
  6. $.ajax({
  7. url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?$Select=Title,EmployeeName,Salary,Dept/Title,Dept/Code&$expand=Dept",
  8. type: "GET",
  9. headers: { "Accept": "application/json;odata=verbose" }, // return data format
  10. success: function (data) {
  12. var table = $("#tblEmployees");
  13. var html = "<thead><tr><th>Title</th><th>EmployeeName</th><th>Salary</th><th>DepartmentName</th><th>DepartmentCode</th></tr></thead>";
  15. for (var i = 0; i < data.d.results.length; i++) {
  16. var item = data.d.results[i];
  18. html += "<tr><td>" + item.Title + "</td><td>" + item.EmployeeName + "</td><td>" + item.Salary + "</td><td>" + item.Dept.Title + "</td><td>" + item.Dept.Code + "</td></tr>";
  20. }
  21. table.html(html);
  22. },
  23. error: function (error) {
  24. alert(JSON.stringify(error));
  25. }
  26. });
  28. }

Finally, App.js file looks like below.

  1. 'use strict';
  2. var listName = "ListB";
  3. ExecuteOrDelayUntilScriptLoaded(initializePage, "sp.js");
  5. function initializePage() {
  6. var context = SP.ClientContext.get_current();
  7. var user = context.get_web().get_currentUser();
  9. // This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model
  10. $(document).ready(function () {
  11. GetEmployeeDetails();
  12. });
  14. function GetEmployeeDetails() {
  16. $.ajax({
  17. url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?$Select=Title,EmployeeName,Salary,Dept/Title,Dept/Code&$expand=Dept",
  18. type: "GET",
  19. headers: { "Accept": "application/json;odata=verbose" }, // return data format
  20. success: function (data) {
  21. //console.log(data.d.results);
  23. var table = $("#tblEmployees");
  24. var html = "<thead><tr><th>Title</th><th>EmployeeName</th><th>Salary</th><th>DepartmentName</th><th>DepartmentCode</th></tr></thead>";
  26. for (var i = 0; i < data.d.results.length; i++) {
  27. var item = data.d.results[i];
  28. //$("#tblEmployees").append(item.Title + "\t" + item.Salary + "\t" + item.Address + "<br/>");
  31. html += "<tr><td>" + item.Title + "</td><td>" + item.EmployeeName + "</td><td>" + item.Salary + "</td><td>" + item.Dept.Title + "</td><td>" + item.Dept.Code + "</td></tr>";
  34. }
  35. table.html(html);
  36. },
  37. error: function (error) {
  38. alert(JSON.stringify(error));
  39. }
  40. });
  42. }
  43. // This function prepares, loads, and then executes a SharePoint query to get the current users information
  44. function getUserName() {
  45. context.load(user);
  46. context.executeQueryAsync(onGetUserNameSuccess, onGetUserNameFail);
  47. }
  49. // This function is executed if the above call is successful
  50. // It replaces the contents of the 'message' element with the user name
  51. function onGetUserNameSuccess() {
  52. $('#message').text('Hello ' + user.get_title());
  53. }
  55. // This function is executed if the above call fails
  56. function onGetUserNameFail(sender, args) {
  57. alert('Failed to get user name. Error:' + args.get_message());
  58. }
  59. }
Before deploying the solution, give the appropriate permissions under AppManifest file.

SharePoint 2013

Try from your side and please let me know if you have any queries. I am attaching the code for your reference.