I have 5 tables that have transactions, between the columns of each table is "Date" which is the date of the transaction.
I need to make a select where I can buy the 5 tables and see if they have transaction for a predetermined date range.
I have 5 tables that have transactions, between the columns of each table is "Date" which is the date of the transaction.
I need to make a select where I can buy the 5 tables and see if they have transaction for a predetermined date range.
With assumed names, and assuming they contain fk with each other:
SELECT * FROM tabla1 T1
INNER JOIN tabla2 T2 ON T1.unit = T2.unit
INNER JOIN tabla3 T3 ON T2.unit = T3.unit
...
WHERE T1.DATE BETWEEN @fecha1 AND @fecha2
AND T2.DATE BETWEEN @fecha1 AND @fecha2
...
AND T5.DATE BETWEEN @fecha1 AND @fecha2
Test with INNER JOIN and conditional you only need to indicate in the JOIN that the field is between your date range, the WHERE would be used if none of the tables depend on another by a KEY . I'm assuming that the TABLA1 is the main table that contains the PRIMARY KEY for the other tables otherwise it applies the WHERE :
SELECT
A.Date,
B.Date,
C.Date,
D.Date,
E.Date
FROM TABLA1 A
INNER JOIN TABLA2 B ON A.unit = B.unit AND B.Date BETWEEN @FECHA_INICIO AND @FECHA_FIN
INNER JOIN TABLA3 C ON A.unit = C.unit AND C.Date BETWEEN @FECHA_INICIO AND @FECHA_FIN
INNER JOIN TABLA4 D ON A.unit = D.unit AND D.Date BETWEEN @FECHA_INICIO AND @FECHA_FIN
INNER JOIN TABLA5 E ON A.unit = E.unit AND E.Date BETWEEN @FECHA_INICIO AND @FECHA_FIN
--WHERE A.Date BETWEEN @FECHA_INICIO AND @FECHA_FIN