Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread not in select statement
Wed, Feb 14 2007 12:08 PMPermanent Link

"Harry de Boer"
LS

dbIsam 3.3

select distinct id_object from rentalobjects, contractrows
where rentalobjects.id_object = contractrows.id_object
and ( date_from <= :d2 and date_till >= :d1)

selects all objects that are rented in a certain period.

select distinct id_object from rentalobjects, contractrows
where rentalobjects.id_object = contractrows.id_object
and not ( date_from <= :d2 and date_till >= :d1)

should select all the objects that are free in that period, I thought.
However that's not the case. What's the right way to do this?

Regards, Harry

Wed, Feb 14 2007 2:07 PMPermanent Link

Sean McCall
Harry,

Wouldn't that return items that have been rented at anytime outside the
period you are looking at? My SQL is a bit rusty, so I can't write it
out for you, but I would try selecting all the rentalobjects where the
count of contract rows matching the period is zero.

Sean

Harry de Boer wrote:
> LS
>
> dbIsam 3.3
>
> select distinct id_object from rentalobjects, contractrows
> where rentalobjects.id_object = contractrows.id_object
> and ( date_from <= :d2 and date_till >= :d1)
>
> selects all objects that are rented in a certain period.
>
> select distinct id_object from rentalobjects, contractrows
> where rentalobjects.id_object = contractrows.id_object
> and not ( date_from <= :d2 and date_till >= :d1)
>
> should select all the objects that are free in that period, I thought.
> However that's not the case. What's the right way to do this?
>
> Regards, Harry
>
>
Thu, Feb 15 2007 4:15 AMPermanent Link

"Harry de Boer"
Sean,

> Wouldn't that return items that have been rented at anytime outside the
> period you are looking at?

Yes, ofcourse, you're right. Tried a couple of other statements, but I don't
seem to get it right, anyone knows how to deal with this.

Regards, Harry


"Sean McCall" <someone@somewhere.net> schreef in bericht
news:13BB3C2D-1EA7-492D-8C36-B8B3CBC9C8DB@news.elevatesoft.com...
> Harry,
>
> Wouldn't that return items that have been rented at anytime outside the
> period you are looking at? My SQL is a bit rusty, so I can't write it
> out for you, but I would try selecting all the rentalobjects where the
> count of contract rows matching the period is zero.
>
> Sean
>
> Harry de Boer wrote:
> > LS
> >
> > dbIsam 3.3
> >
> > select distinct id_object from rentalobjects, contractrows
> > where rentalobjects.id_object = contractrows.id_object
> > and ( date_from <= :d2 and date_till >= :d1)
> >
> > selects all objects that are rented in a certain period.
> >
> > select distinct id_object from rentalobjects, contractrows
> > where rentalobjects.id_object = contractrows.id_object
> > and not ( date_from <= :d2 and date_till >= :d1)
> >
> > should select all the objects that are free in that period, I thought.
> > However that's not the case. What's the right way to do this?
> >
> > Regards, Harry
> >
> >

Thu, Feb 15 2007 4:57 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Harry


Try a script.

1. select objects that are rented during that period and stuff id's into a memory table
2. select everything else where id isn't in the memory table

as a guess

select distinct id_object into "memory\temp" from rentalobjects, contractrows
where rentalobjects.id_object = contractrows.id_object
and ( date_from <= :d2 and date_till >= :d1);
select distinct id_object where id_object not in (select * from "memory\temp);

Roy Lambert
Thu, Feb 15 2007 5:37 AMPermanent Link

"Harry de Boer"
Roy ,

Thanks a lot. That did the trick, great!

Changed it to (3.3. syntax, and using the 'from table' in the last
statement)

select distinct id_object into MEMORY TEMP from rentalobjects, contractrows
where rentalobjects.id_object = contractrows.id_object
and ( date_from <= :d2 and date_till >= :d1);
select distinct id_object FROM OBECTEN where id_object not in (select *
from MEMORY TEMP);

Regards, Harry


"Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
news:8E8D5A7F-56B4-44BE-9BA7-68BCEECF3374@news.elevatesoft.com...
> Harry
>
>
> Try a script.
>
> 1. select objects that are rented during that period and stuff id's into a
memory table
> 2. select everything else where id isn't in the memory table
>
> as a guess
>
> select distinct id_object into "memory\temp" from rentalobjects,
contractrows
> where rentalobjects.id_object = contractrows.id_object
> and ( date_from <= :d2 and date_till >= :d1);
> select distinct id_object where id_object not in (select * from
"memory\temp);
>
> Roy Lambert
>

Image