Is there a way I can write a derived query in for a CRM Plugin? Newbie on CRM dev here.
Query looks like this:
SELECT * FROM table1
WHERE table1.ID1 = XXXX AND table1.ID2 NOT IN (
SELECT table2.ID1
FROM table2
WHERE table2.ID2 = XXXX)
Writing the code using a queryexpression.
Unfortunately these kind of complex sql queries cannot be achieved via fetchxml or queryexpression queries. Especially like Subqueries, Not In scenarios.
Probably you need multiple resultset (EntityCollection), one for table1 & another one for table2, then transversing through it.
Another choice is LINQ queries, you can try.
On a side note, you can vote this idea to improve the querying ability.