Hi everyone,
I am trying to implement a subquery in the WHERE condition.
My query is similar to below,
SELECT [org].[ref],
[users].[ref]
FROM [req]
LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
WHERE org.org_id NOT IN (SELECT org_hier.child_id
FROM [org]
INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
WHERE [org].[ref] = 'India')
One of the ways I found to achieve the above was using antijoin with which I am
able to implement the below query
SELECT [org].[ref]
FROM [req]
LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
WHERE NOT EXISTS (SELECT 1
FROM [org]
INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
WHERE [org].[ref] = 'India' AND [req].[location_id] = [org_hier].[child_id])
But when I try to modify the relbuilder for the above query to add additional
scans and joins for the 'users' and 'appl' tables, my output query remains the
same.
I would really appreciate any help with this.
Thank you.
Regards,
Hrudaya
This message, together with any attachments, is intended only for the use of
the individual or entity to which it is addressed and may contain confidential
and/or privileged information. If you are not the intended recipient(s), or the
employee or agent responsible for delivery of this message to the intended
recipient(s), you are hereby notified that any dissemination, distribution or
copying of this message, or any attachment, is strictly prohibited. If you have
received this message in error, please immediately notify the sender and delete
the message, together with any attachments, from your computer. Thank you for
your cooperation.
