1

I have 2 lists with a parent child relationship.

List A

ID    Title
----------------
1     First
2     Second
3     Third

List B

ID    Title    List A Lookup
----------------------------
1     Value 1   1;#First
2     Value 2   1;#First
3     Value 3   2;#Second
4     Value 4   2;#Second
5     Value 5   2;#Second

What I'd like to do is identify all of the items in List A that have child records in List B. Given the above data, the results should be:

ID    Title
----------------
1     First
2     Second

CAML has an IN operator, but it looks like that requires you specify the individual values, rather than using another query as the source of the values.

In SQL, I'd write this:

SELECT ID, Title
FROM ListA
WHERE ID IN (SELECT ListALookup FROM ListB)

Is this possible? Or do I need to query all items from List B, loop through, and track the distinct List A values from there?

If there is a solution to this outside of CAML, I'm open to that as well, as long as I can implement it in JavaScript.

1

The easiest way it to create a lookup column from 'List A' to 'List B' and as column select 'List A Lookup (Count Related)' (This will only show up because 'List B' is already related to 'List A' through this column).

Now you have a column containing how many items in 'List B' each value from 'List A' have relating to it and can filter on that being greater than zero.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.