How can I filter a table with data from another in mysql?

Francisco J Garcia Corbeira 0
Viewed 44

I have two columns with dates and times. I would like to get all the data from another table that is between the times in column ort and ocas. For example, all the data between: 1/1/2012 9:04 AM to 1/1/2012 6:12 PM AND 2/ 1/2012 9:04 to 1/2/2012 18:13 etc

Columns

data table

select * FROM camarinas
WHERE  data
 between (select ort from ort_ocas) 
 and (select ocas from ort_ocas);

Error Code: 1242. Subquery returns more than 1 row

SELECT * FROM camarinas
  WHERE `data` IN (
    SELECT DISTINCT `data` FROM camarinas
      JOIN ort_ocas
      ON `data` BETWEEN Ort AND Ocas  
  );

Error Code: 2013. Lost connection to MySQL server during query 30.016 sec

mysql
1 Answers

JOIN ON..between

DROP TABLE IF exists t,t1;

create table t
(id int auto_increment primary key, dt date);
create table t1
(dt1 date,dt2 date);

insert into t(dt) values
('2022-08-10'),('2022-09-01'),('2022-10-01');
insert into t1 values
('2022-08-01','2022-08-31'),
('2022-09-01','2022-09-30');

select id,dt,dt1,dt2 
from t
join t1 on dt between dt1 and dt2;

+----+------------+------------+------------+
| id | dt         | dt1        | dt2        |
+----+------------+------------+------------+
|  1 | 2022-08-10 | 2022-08-01 | 2022-08-31 |
|  2 | 2022-09-01 | 2022-09-01 | 2022-09-30 |
+----+------------+------------+------------+
2 rows in set (0.001 sec)
P.Salmon0
Related