Rodrigo Rodrigo - 17 days ago 7
MySQL Question

Create TRIGGER in MySQL - need to perform INTERSECT function

Basically i want to perform these two queries and perform an intersect operation:

(SELECT DISTINCT C.hospital_id FROM hospital.consulta C
INNER JOIN hospital.enfermaria E WHERE C.consulta_id = 2)
-- INTERSECT
(SELECT DISTINCT E.hospital_id FROM hospital.enfermaria_atendimento EA
INNER JOIN hospital.enfermaria E WHERE E.enfermaria_id = 1);


However, seems like there is no such thing in MySQL (INTERSECT). This is to allow me to create a TRIGGER BEFORE INSERT in my table hospital.enfermaria_atendimento to ensure that every tuple is consistent.

enfermaria_atendimento table contains consulta_id and enfermaria_id attributes. Both attributes are foreign keys to different tables (table consulta and table enfermaria) which (both) have a relationship with table hospital. A new tuple to enfermaria_atendimento cannot contain a consulta_id related to a enfermaria_id that belongs to a different hospital_id.

I hard coded specific enfermaria_id=1 and hospital_id=2 just for simulation purpose. Instead of specific numbers i intend to use the information from the new. insert

I tried to simulate the INTERSECT parameter with this:

SELECT DISTINCT E.hospital_id FROM hospital.enfermaria_atendimento EA
INNER JOIN hospital.enfermaria E WHERE E.enfermaria_id = 1
WHERE E.hospital_id
IN (
SELECT C.hospital_id FROM hospital.consulta C
INNER JOIN hospital.enfermaria_atendimento EA ON C.consulta_id=2
);


But no success so far...

Answer

I believe that INTERSECT in your case could be simulated like below (using additional join):

SELECT DISTINCT E.hospital_id 
FROM hospital.enfermaria_atendimento EA 
INNER JOIN hospital.enfermaria E ON 
  ?? = ?? -- put conditions here
INNER JOIN (
  SELECT DISTINCT C.hospital_id
  FROM hospital.consulta C
  INNER JOIN hospital.enfermaria_atendimento EA ON 
    ?? = ?? -- put conditions here
  WHERE C.consulta_id = 2
  ) F ON E.hospital_id = F.hospital_id
WHERE
  E.enfemaria_id = 1

You have to specify joining conditions, they are missing in your query.