How can I join these two SQL queries?

Question

How can I join these two SQL queries?

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I need to join this query

SELECT CodContrato, FechaBaja, FechaFin
FROM Contrato
WHERE (
       (FechaBaja != FechaFin)
        OR (FechaBaja != **SEGUNDA CONSULTA**
AND (FechaBaja BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')

with this other

SELECT CodContrato, FechaFinProrroga
FROM DatoAuxiliarContrato
WHERE FechaFinProrroga != '' AND (FechaFinProrroga BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')

Both tables have a common field that is CodContrato

I'm trying with INNER JOIN but I do not succeed. Can you help me? Thanks in advance

mysql sql query

asked by Paolo Frigenti 11.12.2018 в 12:38

source

2 answers

1

You can try with union, although you have to take into account that in the SELECT part the columns have to be the same in both parts of the union (or substitute the asteric of the first for a single field or in the second add as many columns to the date as the contract table has

SELECT *
FROM Contrato
WHERE 
(
    (FechaBaja != FechaFin)
    OR
    (FechaBaja != **SEGUNDA CONSULTA**)         
)
AND (FechaBaja BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')
UNION
SELECT FechaFinProrroga
FROM DatoAuxiliarContrato
WHERE 
(FechaFinProrroga != '') AND (FechaBaja BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')

answered by 11.12.2018 в 12:45

What is the best way to include classes in php? Error adding columns to MySql table

score 3 · Accepted Answer

SELECT *
FROM Contrato
WHERE (
       (FechaBaja != FechaFin)
        OR (FechaBaja NOT IN (SELECT FechaFinProrroga
                                FROM DatoAuxiliarContrato
                               WHERE (FechaFinProrroga != '') AND (FechaFinProrroga BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')
                              )  
            )         
      )
AND (FechaBaja BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')

The operator IN will be true if the value is within the following list (a subquery is treated as a list).

If you want or know that there will only be one value in the subquery you can do:

SELECT *
FROM Contrato
WHERE (
       (FechaBaja != FechaFin)
        OR (FechaBaja != (SELECT FechaFinProrroga
                                FROM DatoAuxiliarContrato
                               WHERE (FechaFinProrroga != '') AND (FechaFinProrroga BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')
                               LIMIT 1 
                              )  
            )         
      )
AND (FechaBaja BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')

Look at LIMIT 1 to ensure that no more than one value will be returned, which would give an error in the query.

You can find more information about the use of subqueries in the official documentation link

(edit)

According to the latest clarifications, it would be:

SELECT c.*
  FROM Contrato AS c
 WHERE (
       (c.FechaBaja != c.FechaFin)
        OR (c.FechaBaja NOT IN (SELECT aux.FechaFinProrroga
                                FROM DatoAuxiliarContrato AS aux
                               WHERE (aux.FechaFinProrroga != '') 
                                 AND (aux.FechaFinProrroga BETWEEN '20180101 00:00:00' AND '20181231 23:59:59') 
                                 AND (aux.CodContrato = c.DatoAuxiliarContrato)
                              )  
            )         
      )
 AND (c.FechaBaja BETWEEN '20180101 00:00:00' AND '20181231 23:59:59')

I have added alias to the tables, to clarify a bit because FechaBaja generates a lot of doubts.