[SOLVED] How to intersect 2 subqueries

How to intersect 2 subqueries

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.

Solution

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?;