CODEWITHSUNDEEP
mysqlsqlplsql
Smiley
Smiley

Reputation: 108

Need Help on .... Select Where Date NOT BETWEEN

Hope someone can tell ..

Table A                  Table E
Id  |  Date              Id  |  Start_date  |  End_date
 1     2012-12-10         1     2012-12-09     2012-12-10
 2     2012-12-11         2     2012-12-12     2012-12-14

The Result that I'm hoping ..
2012-12-11

This is the code that I think might work to select date from Table A that not in Table E ranga date... 

SELECT * FROM `A` 
WHERE `A`.`DATE` NOT BETWEEN (SELECT `E`.`DATE_START` FROM `E`) AND (SELECT `E`.`DATE_END`       
FROM `E`);

but unfortunately not, the subquery return more than 1 row.

I wonder how??

thanks

Upvotes: 0

Views: 165

Answers (4)

David Aldridge
David Aldridge

Reputation: 52376

What you're looking for here is the set of records in A where there does not exist a record in B for which the date in A is between the begin and end dates in B.

Therefore I'd suggest that you structure the query in that way.

Something like ...

 Select ...
 From   table_A
 Where  not exists (
          Select null
          From   table_b
          Where  ...)

Depending on the join cardinality of the tables and their sizes you may find that this performs better than the "find the rows that are not in the set for which a John exists" method, aside from it being a more intuitive match to your logic.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 882616

You wonder how the subquery returned more than one row? That's because there's more than one row in the table matching your query.

If you want one row, you'll need to limit the query a little more, such as with:

select `e`.`date_start` from `e` where `e`.`id` = 1

If you want all dates in A that are not contained in any date range in E, one way to do it is to get a list of the A dates that are contained within a range, and then get a list of dates from A that aren't in that list.

Something like:

select date
from   a
where  date not in (
           select a.date
           from   a, e
           where a.date between e.start_date and e.end_date
       )

Putting this through the excellent phpMyAdmin demo site as:

create table a (id int, d date);
create table e (id int, sd date, ed date);
insert into a (id, d) values (1, '2012-12-10');
insert into a (id, d) values (2, '2012-12-11');
insert into e (id, sd, ed) values (3, '2012-12-09', '2012-12-10');
insert into e (id, sd, ed) values (4, '2012-12-12', '2012-12-14');
select d from a where d not in (
    select a.d from a, e where a.d between e.sd and e.ed
    );

results in the output:

2012-12-11

as desired.

Upvotes: 1

Guffa
Guffa

Reputation: 700810

To get all records from A that are not inside any of the date ranges in E, get the records that are within the date ranges, and select the ones not in that result:

select *
from A
where Id not in (
  select A.Id
  from A
  inner join E on A.Date between E.Start_date and E.End_date
)

Upvotes: 0

xlecoustillier
xlecoustillier

Reputation: 16361

If the Id in table A is the same as the Id in table E :

SELECT *
FROM A, E
WHERE A.Id = E.Id
AND A.Date NOT BETWEEN E.Start_Date AND E.End_Date

Upvotes: 0

Related Questions