The error is stating that there are 2 columns by the same name (in this case there is a field named "id" in both tables). Your best bet would be to alias one or both of the id columns to another name which should resolve the error.
first, thank you for your reply.
second, the custom query used to work fine with me joining the same tables earlier on tableau prep and it worked, now it's not working anymore.
third, I don't have access rights to rename 'id' column in every table I am joining, even with an alias is not working