HomeActive Query Builderhow can add "INNER JOIN", or remove fields, from both subqueries?

how can add "INNER JOIN", or remove fields, from both subqueries?


Hello,

I used Query Modification Demo again. And I would like to implement a scenario that I ignored:

when I build a query composed of UNION, or INTERSECT, I would like to add the same new INNER JOIN clause to both subqueries, or remove fields from both subqueries to make sure that they contains the sames fields in the same order.

So beginning with "SELECT *
FROM Customers c
UNION
SELECT *
FROM Customers"

I would like to get
"SELECT * FROM Customer c INNER JOIN Orders o ON ..."
UNION "SELECT * FROM Customer c INNER JOIN Orders o ON ..."

or,

"SELECT customerId FROM Customer INNER JOIN ...
UNION
SELECT c.customerId FROM c.Customer INNER JOIN .... ON ..."

However with the current sample, when I apply changes to a query combined by UNION, I can only apply changes (INNER JOIN) to the second subquery:

"SELECT *
FROM Customers c
UNION
SELECT *
FROM Customers INNER JOIN
Orders o ON Customers.CustomerID = o.CustomerID
WHERE Customers.CompanyName LIKE 'C%'"

So what should I do to make sure that both subqueries in a combined query are modified simultaneously?

Thanks.
Rosière Fontaine | email
October 20 2009, 10:15 AM
Dear Rosière,

In your case, the QueryBuilder.Query is a UnionGroup object that contains a list of union subqueries or nested groups. You should perform the required modifications to each subquery in the group.

An example of iteration through all union sub-queries could be found in the Non-visual demo, see the LoadQuery and LoadUnionGroup procedures.
Sergey Smagin | email
13 hours, 55 mins since original post
This topic is closed