Lars,
if I understand you correctly:
create table table1
(
hotel_number int
);
create table table2
(
hotel_number int,
free_day datetime
);
insert into table1 values(1);
insert into table1 values(2);
insert into table1 values(3);
insert into table2 values(1,"Jan 1 2003");
insert into table2 values(1,"Jan 2 2003");
insert into table2 values(3,"Jan 1 2003");
Then:
select distinct t1.hotel_number, count(t2.free_date)
from table1 t1, table2 t2
where t2.hotel_number = t1.hotel_number
group by t1.hotel_number;
will give:
hotel_number free_date
------------ --------------------------
1 Jan 1 2003 12:00AM
1 Jan 2 2003 12:00AM
3 Jan 1 2003 12:00AM
Is this what you are looking for?
Keith
Extranet
[EMAIL PROTECTED] - 08/02/2003 17:59
Please respond to [EMAIL PROTECTED]
To: mysql
cc:
Subject: Need help with JOIN
Heyho folks,
I am sitting now for a few weeks on a tricky problem. I don't get it -
maybe
anyone of you could give me a hint.
Let's say I have two tables :
Table One has a lot of information about travels, hotels, flights etc.
Table Two has an entry for each hotel and each day where I store
information
about price and most important if this hotel is free on this specific day.
Now I want to gather a list of all hotels I could book in a certain
time-range.
For that I need to issue some quite complex query in table One which is not
at all a problem. But now I need to know if all days in my timeframe are
bookable.
The easiest way to do this is to query table Two for the timeframe I am
looking for and issuing a "count()" - and this I compare with the days
needed and - voila I know if this hotel has a free room for each day.
This works - but I do have a serious performance problem here. I need to
issue this second SELECT on my Table Two for each hotel I found in table
one - and as this are easily more than 2000 I get 2000 additional queries.
Now I thought about doing another join on my first select - but the problem
here is, that I get a huge amount of rows then. Quite clear :) - I have a
record for each day in table Two - so I get hotels * days rows - and this
easily exceeds half a million rows and more.
The best solution I am searching for is now a possibility that I get the
count() of rows in tabel Two somehow in my select on table One.
OK maybe it's a little bit confusing :)
I try to resume the problem :
I do a select on table One.
And I need to know the count() of rows in table Two with my ID which I get
from the first select.
I cant do a join as I then get NrofFoundRows in Table One * NrofFoundRows
in
Table Two entries.
Any ideas ?
dodger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.
---------------------------------------------
Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
