Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 5 of 5 total |
not in select statement |
Wed, Feb 14 2007 12:08 PM | Permanent 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 PM | Permanent 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 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 > |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |