I have two lists with look up column. How do i join two lists using REST API call.
Thanks in advance
1 Answer
You use the $expand query option.
For example, you have two lists: ListA and ListB. ListA has two fields: Field1 and Field2. ListB has three fields: FieldA, FieldB and LookupField. LookupField looks up to ListA. REST query should be as such:
/_api/web/lists/GetByTitle('ListB')/items?$select=FieldA,FieldB,LookupField/Field1,LookupField/Field2&$expand=LookupField
You select the fields from the list by adding their internal name to the $select query option, and you select fields from the lookup list by using the format LookupField/FieldFromLookupList, and then you add the lookup field to the $expand query option.
-
-
For fields from the child list (the one with the lookup field), simply add them to the
$selectportion. For fields from the parent (the lookup list), add them to the$selectportion in the formatLookupField/FieldFromLookupList, whereLookupFieldis the internal name of the lookup field in the child list (all field names must be the internal names).– wjervisCommented Jun 11, 2015 at 12:06 -
Means correct me if am wrong, I can directly specify the child list field name in $select portion, If i need parent list fields i should use lookupfield/fieldname right ?– VijayCommented Jun 11, 2015 at 12:23
-
Fields from the list that contains the lookup field can be directly specified in the $select portion. Fields from the list being looked up to have to be specified using
lookupfield/fieldname.– wjervisCommented Jun 11, 2015 at 12:33 -