mySQL Subtract table result of JOINS to an original table

Question

mySQL Subtract table result of JOINS to an original table

Navigation

#1 by (0 votes)

1

It turns out that after using a SELECT * FROM + several JOINS such that ...

SELECT * FROM EMBARCACION RIGHT JOIN 
(SELECT * FROM EMBARCACION LEFT JOIN POSICION
  ON EMBARCACION.matricula=POSICION.matricula JOIN VENTA
  ON VENTA.matricula=EMBARCACION.matricula JOIN SOCIO 
  ON SOCIO.IDSocio=VENTA.IDSocio  
  WHERE POSICION.IDposicion is not null AND POSICION.fechaFinal is null
) WHERE POSICION.IDPosicion is null

As a result I get a table.

To the original BOARD table I want to subtract the table I have obtained with the SELECT but in the same sentence and without having to create a physical table.

Any ideas on how to do it?

mysql

asked by olbaPGo 23.05.2017 в 13:54

source

1 answer

Mail server problem Color row where cell has value of arraylist

score 0 · Answer 1

This query should do what you ask:

DELETE FROM EMBARCACION WHERE embarcacion_matricula in 
SELECT embarcacion_matricula FROM EMBARCACION RIGHT JOIN 
(
     SELECT * FROM EMBARCACION LEFT JOIN POSICION
     ON EMBARCACION.matricula=POSICION.matricula JOIN VENTA
     ON VENTA.matricula=EMBARCACION.matricula JOIN SOCIO 
     ON SOCIO.IDSocio=VENTA.IDSocio  
     WHERE POSICION.IDposicion is not null AND POSICION.fechaFinal is null
)
WHERE POSICION.IDPosicion is null

Basically it is a delete from ship where the registration (or the id that corresponds) is within the set of id's (IN) that returns the subquery

SELECT embarcacion_matricula FROM EMBARCACION RIGHT JOIN 
    (
         SELECT * FROM EMBARCACION LEFT JOIN POSICION
         ON EMBARCACION.matricula=POSICION.matricula JOIN VENTA
         ON VENTA.matricula=EMBARCACION.matricula JOIN SOCIO 
         ON SOCIO.IDSocio=VENTA.IDSocio  
         WHERE POSICION.IDposicion is not null AND POSICION.fechaFinal is null
    )
    WHERE POSICION.IDPosicion is null

I hope it's useful!