problems showing field values in query 1

[vikunja]

I have made a simple database that will make reservations. The main fields are name, date of reservation and bed number. I have tried to make a query which will show all the beds that are free given a specific date. But it will not show any beds that haven't been added to a record.

For an example: two beds with number 305-1, 305-2 have been reserved, but the bed field (in the table) has four other beds, with the values 305-3 to 305-6. The query I was hoping of making shows the last mentioned beds which hasn't been reserved.

Thanks for the help in advance

Alexi

 

[Steve101]

Alex,

If you publish the SQL of the query, we'll be able to provide better help to you (you can cut and paste it view the View, SQL View menu options,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[vikunja]

Hi Steve

This is the SQL query

SELECT bed.bednr
FROM bed INNER JOIN reservation ON bed.bedid = reservation.bedid
WHERE (((reservation.arrival) Between [first date] And [second date]));

I have tried a few different criteria options on bednr, but has not helped in showing the bednr which isn't used in the records

thanks!

Alexi

 

[Steve101]

Alexi,

Try the following:
[tt]
SELECT Bed.BedNr, Bed.BedId
FROM Bed
WHERE BedId Not In (SELECT BedId
FROM Reservation
WHERE Arrival BETWEEN [Start] and [End]);
[/tt]

This is called a correlated subquery; basically a query in a query, correlated in this case, by the common BedId field.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[vikunja]

Hi Steve

It worked!

Thank you very much for your help

Alexi

 

[vikunja]

Hi Steve

I just found out that the query should show all free beds within a specific period of time (arrival and departure), now it just shows the free beds for a specific period from the arrival date. I do have a departure field aswell. Is there any way i can redo the above query you gave me?

thanks

Alexi

 

[Steve101]

Alexi,

Try the following small variation:
[tt]
SELECT Bed.BedNr, Bed.BedId
FROM Bed
WHERE BedId Not In (SELECT BedId
FROM Reservation
WHERE Arrival >= [Start]
AND Departure <= [End]);
[/tt]
I think that will do the trick,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)