Jump to content

difficult sql query question


ephemerae

Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.