I want to change select query into FetchXML Query. My query is
SELECT * FROM new_periodtotal
JOIN product ON product.pruductid = new_periodtotal.product
JOIN new_program ON new_program.product = new_periodtotal.product
Problem is there is no direct relationship with new_periodtotal and new_periodtotal both have relationship with Product. how I convert it in fetchXML query.
I don't have a structure like that at hand to test this out, but I'll try to extrapolate a solution based on the OP query. Some additional tuning / trial&error might be required.
If product.productid = new_periodtotal.product and new_program.product = new_periodtotal.product then product.productid = new_program.product.
You in fact have a realtionship, though it's "indirect": both entities would have non-empty "product" fields.
To obtain the FetchXML I would build an Advanced Find like this:
Search entity: product
The third row is meant to look in the related of the related, hence the increased indentation.
It would read like this: "get all product records where the related new_periodtotal records have a non-empty product field, and where the related new_program records of the related new_periodtotal records have non-empty product field".
After that, you can Download FetchXML (up in the ribbon) to get the query.
