difficult sql query question

[ephemerae]

Salut!

 

I\'m working on a small search engine for an internet rental service and got stuck with a quite difficult sql (at least for me) problem, hope one of you can help!

 

 

Since the overall thing is quite complicated I\'ll try to simplify it a bit:

 

Table 1: Homes

(contains all information about the homes we offer etc.

important here: the hid = home id)

 

Table 2: Occupancy

(contains all information about the renter etc.

important here: every entry is linked to the right home via hid

+ there are 2 fields RentFrom and RentTo which mark the from and enddate of a single rental)

 

Search Html:

The User can among other options specify the start and enddate in which he\'s interested in here.

 

So how can i check the Occupancy against the User\'s Search-Query and return the right homes for this timespan with a single SQL statement?

(I know it\'s possible to work around that via PHP, but i need it that way...)

 

Your help is appreciated!

eph!

[pallevillesen]

Just to clarify before I start thinking:

 

1. Select all homes which are vacant in the queried time interval.

2. Keep all these homeIDs.

3. Return all homes names that have their ID in (2).

 

Is this what you want?

 

This is easiest done with a temporary table, which lists all the vacant \'hid\'.

Then join this with your home table and voila...

 

If still in trouble - reply here...

 

P., denmark

[ephemerae]

you got it!

that\'s how it should work.

 

what do you mean by using a temporary table?

 

isnt there a \"SELECT * .... LEFT JOIN ...\" solution?

[pallevillesen]

OK,

 

I\'m not sure if you are able to use a single sql statement, but maybe (my gut feeling is no).

 

Temporary tables are smart, easy and quick.

[php:1:cc66c890c2]

$temp = mysql_query(\"create temporary table temphomes as select distinct homeID from Occu... WHERE....;\");

$result = mysql_query(\"select homename from temphomes where homename.homeID=temphomes.homeID;\");

$temp = mysql_query(\"drop table temphomes;\");

$temp = \"\";

[/php:1:cc66c890c2]

 

P.