On Sun, June 26, 2005 3:38 am, Pedro Quaresma de Almeida said: > I have two databases, on for aeromodelistas (aeromodelling) and > another for Códigos Postais (Postal Codes). I whant to do the > following query First, MySQL *DOES* allow you to select from multiple databases in a single query. Most RDBMS do not have that feature, AFAIK. > SELECT CódigoPostal FROM Aeromodelistas > WHERE CódigoPostal IN > (SELECT distinct(CP4) FROM codigopostal.LOCART,codigopostal.DISTRITO > WHERE codigopostal.LOCART.DD=codigopostal.DISTRITO.DD > AND codigopostal.DISTRITO.DESIG='Coimbra'); > > This query is not working, and I do not know why. If I try the two > queries individualy they work, togheter they don't!? As noted, maybe your MySQL version doesn't support sub-queries. Are you using mysql_error() to find out what went wrong? Cuz MySQL is practically BEGGING you to ask it "What went wrong?" http://php.net/mysql_error > But the question I want to put to the members of this list is the > following. Is it possible to do the following? > > // first do the subquery > $sql_CP4s = "select distinct(CP4) from > codigopostal.LOCART,codigopostal.DISTRITO where > codigopostal.LOCART.DD=codigopostal.DISTRITO.DD and > codigopostal.DISTRITO.DESIG='$nomeDistrito'"; > > $resultado_CP4s = mysql_query($sql_CP4s,$ligacao); > > $linha_CP4s = mysql_fetch_assoc($resultado_CP4s); > > // then use it in the main query > > $sql_Aero_Dist_Masc = "select count(Nome) from Aeromodelistas where > year(AnoQuota)=2005 and Sexo='Masculino' and Distrito IN $linha_CP4s"; Yes, but... You'll need to work on the syntax a bit. //Add commas for valid SQL in an IN expression: $linha_CP4s_sql = implode(", ", $inha_CP4s //Add parens for valid SQL in an IN expression: "... and Distrito IN ($linha_CP4s_sql) "; > Is it possible? Generally, it's SLOWER than using a JOIN or a sub-select. Sometimes, particularly if the fields you are searching/joining on are not indexed, it's actually faster. This might be one of those cases, particularly since your Postal Codes table probably has a LOT of entries. For example, if you assume a rather modest number of aeromodellers (say, 2000) and a number of Postal Codes such as in the US of about 60,000 you then have 2000 X 60,000 == 120,000,000 tuples in an unrestricted join. Your basic $20/month webhost doesn't provide anywhere *NEAR* the amount of temp/swap disk space you would need for 120 MILLION tuples to run AT ALL, much less in some kind of reasonable time frame. This could be rule-breaking time. Normally, you would never, ever, ever want to have the same data duplicated in two tables. BUT, if your choice is between 120 MILLION tuples, and intelligently de-normalizing your database, then intelligently de-normalize your database. Specifically: add column to aeromoddellers DD varchar(10) default null; (Only maybe it's int(11) instead of varchar(10) or whatever) Write a cron job that does something like: select id, something from aeromodellers where DD is null limit 100; while (list($id, $something) = mysql_fetch_row($result)){ select DD from posital_coditas where something about $something update aeromodellers set DD = $DD where id = $id } The purpose of the above psuedo-code is to COPY the DD you need from the Postal Codes to the aeromodeller table. Run the script every few minutes for a day or two, then when the table has no NULL DD colums left, every day or so. Add some triggers (or business logic) so that when $something changes, the DD gets reset to NULL, and your cron job will re-copy the DD from the Source Postal Codes. While this is technically "nasty de-normalized data", it is done in such a way that: 1) The DD field is never set directly, only copied from the One True Source 2) The DD field may be NULL, and un-usable, but it's never *WRONG* data. 3) Instead of 120 MILLION tuples crippling your server, you have 2000* * 2000 is really however many rows are in aeromodeller... It's still (1/60000)th of the search space, any way you cut it. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
