How to intersect 2 subqueries

Question

Hello i want to intersect 2 long Queries with each other but i found out that you can only intersect simple Queries. Is this possible to get something like this?

SELECT id, name as antibodyName 
FROM Antibodies 
WHERE id IN (
           (SELECT id FROM Antibodies WHERE name LIKE ?
                   UNION ALL
            SELECT antiId FROM AssignedColors WHERE name LIKE ?
                   UNION ALL
            SELECT antiId FROM AssignedReactivities WHERE name LIKE ?)
         INTERSECT
           (SELECT id FROM Antibodies WHERE name LIKE ?
                   UNION ALL
            SELECT antiId FROM AssignedColors WHERE name LIKE ?
                   UNION ALL
            SELECT antiId FROM AssignedReactivities WHERE name LIKE ?)
)
AND dateOfCreation >= ? AND dateOfCreation <= ?
ORDER BY dateOfCreation DESC LIMIT ? OFFSET ?;

Actually i want to get Intersect of unions of other queries.

Answer 1

You must use a SELECT statement for each group of your unioned queries:

SELECT id, name as antibodyName 
FROM Antibodies 
WHERE id IN (
  SELECT id FROM
  (
    SELECT id FROM Antibodies WHERE name LIKE ?
    UNION all
    SELECT antiId FROM AssignedColors WHERE name LIKE ?
    UNION all
    SELECT antiId FROM AssignedReactivities WHERE name LIKE ?
  )
  INTERSECT
  SELECT id FROM
  (
    SELECT id FROM Antibodies WHERE name LIKE ?
    UNION all
    SELECT antiId FROM AssignedColors WHERE name LIKE ?
    UNION all
    SELECT antiId FROM AssignedReactivities WHERE name LIKE ?
  )
) AND dateOfCreation >= ? AND dateOfCreation <= ?
ORDER BY dateOfCreation DESC LIMIT ? OFFSET?;

Answer 2

If I understand your question correctly, an INNER JOIN would help solve the problem most efficiently. The inner INSERSECT can be changed to the below code, and the outer SELECT can remain the same.

(SELECT id FROM Antibodies WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedColors WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedReactivities WHERE name LIKE ?) A
JOIN
(SELECT id FROM Antibodies WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedColors WHERE name LIKE ?
UNION all
SELECT antiId FROM AssignedReactivities WHERE name LIKE ?) B
ON A.id = B.id