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
Re: Need help with JOIN
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 -- 1Jan 1 2003 12:00AM 1Jan 2 2003 12:00AM 3Jan 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
Need Help with JOIN
Hello, I have 2 tables(pics and outings) pics pic_id, tinyint path, varchar outings outings_id, tinyint pic1_id, tinyint pic2_id, tinyint pic3_id, tinyint pic4_id, tinyint When i try to do a join like this mysql_query(SELECT * FROM pics inner join outings on outings.pic1_id=pic.pic_id and outings.pic2_id=pic.pic_id and outings.pic3_id=pic.pic_id,$db) or die(mysql_error()); Nothing is returned. The query runs ok(nothing from mysql_error). When I drop outings.pic2_id=pic.pic_id and outings.pic3_id=pic.pic_id from the query works and returns the path of the pic. I'm fairly certain that the above returns nothing because of the multiple pic.pic_id's in the query. But I don't know what to do get it working(Redesign? or Is there something I could change in the query?) Thanks in advance for any help, Mike sql,mysql --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02 - 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
Re: Need Help with JOIN
Mike, With the way you have it written below, all of the pic1_id, pic2_id, pic3_id, and pic4_id must be equal (because they all equal pic.pic_id). Since this wasn't true for any of the rows, that is why you were getting no results. To get each picture value seperately, you will have to do one join for each pic#_id, which will give you something like: SELECT * FROM pics AS p1, pics AS p2, pics AS p3, pics AS p4 , outings AS o WHERE o.pic1_id=p1.pic_id AND o.pic2_id=p2.pic_id AND o.pic3_id=p3.pic_id AND o.pic4_id=p4.pic_id; Or as you had it written before using INNER JOIN's: SELECT * FROM outings as o INNER JOIN pics AS p1 ON o.pic1_id=p1.pic_id INNER JOIN pics AS p2 ON o.pic2_id=p2.pic_id INNER JOIN pics AS p3 ON o.pic3_id=p3.pic_id INNER JOIN pics AS p4 ON o.pic4_id=p4.pic_id; Either of those should work fairly well. Hope that helps some. Harrison Mike wrote: Hello, I have 2 tables(pics and outings) pics pic_id, tinyint path, varchar outings outings_id, tinyint pic1_id, tinyint pic2_id, tinyint pic3_id, tinyint pic4_id, tinyint When i try to do a join like this mysql_query(SELECT * FROM pics inner join outings on outings.pic1_id=pic.pic_id and outings.pic2_id=pic.pic_id and outings.pic3_id=pic.pic_id,$db) or die(mysql_error()); Nothing is returned. The query runs ok(nothing from mysql_error). When I drop outings.pic2_id=pic.pic_id and outings.pic3_id=pic.pic_id from the query works and returns the path of the pic. I'm fairly certain that the above returns nothing because of the multiple pic.pic_id's in the query. But I don't know what to do get it working(Redesign? or Is there something I could change in the query?) Thanks in advance for any help, Mike sql,mysql --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02 - 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 - 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
Re: Need Help with JOIN
Thanks for the help. Got that to work no problem. Mike - Original Message - From: Harrison C. Fisk [EMAIL PROTECTED] To: Mike [EMAIL PROTECTED] Cc: mysql list [EMAIL PROTECTED] Sent: Thursday, June 13, 2002 5:48 PM Subject: Re: Need Help with JOIN Mike, With the way you have it written below, all of the pic1_id, pic2_id, pic3_id, and pic4_id must be equal (because they all equal pic.pic_id). Since this wasn't true for any of the rows, that is why you were getting no results. To get each picture value seperately, you will have to do one join for each pic#_id, which will give you something like: SELECT * FROM pics AS p1, pics AS p2, pics AS p3, pics AS p4 , outings AS o WHERE o.pic1_id=p1.pic_id AND o.pic2_id=p2.pic_id AND o.pic3_id=p3.pic_id AND o.pic4_id=p4.pic_id; Or as you had it written before using INNER JOIN's: SELECT * FROM outings as o INNER JOIN pics AS p1 ON o.pic1_id=p1.pic_id INNER JOIN pics AS p2 ON o.pic2_id=p2.pic_id INNER JOIN pics AS p3 ON o.pic3_id=p3.pic_id INNER JOIN pics AS p4 ON o.pic4_id=p4.pic_id; Either of those should work fairly well. Hope that helps some. Harrison Mike wrote: Hello, I have 2 tables(pics and outings) pics pic_id, tinyint path, varchar outings outings_id, tinyint pic1_id, tinyint pic2_id, tinyint pic3_id, tinyint pic4_id, tinyint When i try to do a join like this mysql_query(SELECT * FROM pics inner join outings on outings.pic1_id=pic.pic_id and outings.pic2_id=pic.pic_id and outings.pic3_id=pic.pic_id,$db) or die(mysql_error()); Nothing is returned. The query runs ok(nothing from mysql_error). When I drop outings.pic2_id=pic.pic_id and outings.pic3_id=pic.pic_id from the query works and returns the path of the pic. I'm fairly certain that the above returns nothing because of the multiple pic.pic_id's in the query. But I don't know what to do get it working(Redesign? or Is there something I could change in the query?) Thanks in advance for any help, Mike sql,mysql --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02 - 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 - 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02 - 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
