Jump to content

Archived

This topic is now archived and is closed to further replies.

ephemerae

difficult sql query question

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!

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×

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.