Jump to content


Photo

difficult sql query question


  • Please log in to reply
3 replies to this topic

#1 ephemerae

ephemerae
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 15 January 2003 - 03:09 PM

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!

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 15 January 2003 - 05:37 PM

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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 ephemerae

ephemerae
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 15 January 2003 - 05:44 PM

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?

#4 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 16 January 2003 - 01:48 PM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users