Re: Using Joins/Unions
On Tue, August 4, 2015 16:05, Ryan Coleman wrote: No but there should be. If there's not my task is useless. Secondly yes. Unique name on it too. -- Ryan Coleman Publisher, d3photography.com ryan.cole...@cwis.biz m. 651.373.5015 o. 612.568.2749 On Aug 4, 2015, at 17:33, Wm Mussatto mussa...@csz.com wrote: On Tue, August 4, 2015 11:19, Ryan Coleman wrote: I have been a MySQL user and supporter for over a decade (since 2001) and I am almost ashamed to admit that I haven’t the faintest idea on how to do joins and unions. I have a specific query I would love to run… I have two tables, one with Unique data (“images”) and one with corresponding paths but many replicated records (“files”). I want to run a query that takes the results from /images/ and also searches /images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ in descending order returning just the first record (largest file size). There may be up to 750 records from /images/ and thusly could be 3000+ from /files/. How on earth do I do this? — Ryan First question, will there always be at least one record in the files table for every record in the images table? That controls the kind of join you will use. I don't think that a union is a player. Also, is there a unique record ID in each of the table? Sorry, been swamped. If you can ignore the cases where there are not any entry in the 'Files' table then a simple join will work. Otherwise you would need a LEFT JOIN Assume a structure images.id - unique record ID images.commonName - this will be the common reference name in both ... files.id - unique record ID files.commmonName - this will be the common reference name in both files.location -where this record is on the disk/system files.filesize - numeric field in whatever units you want (say bytes) select images.commonName, files.location, MAX(files.filesize) FROM images, files WHERE images.commonName = files.commonName GROUP BY files.commonName ORDER BY images.commonName Here is my test structure. No doubt someone else can get it optimized. This seems to use a temp table -- -- Table structure for table `files` -- CREATE TABLE `files` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CommonName` varchar(255) NOT NULL, `Location` varchar(255) NOT NULL, `filesize` int(11) NOT NULL, PRIMARY KEY (`ID`), KEY `CommonName` (`CommonName`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `files` -- INSERT INTO `files` (`ID`, `CommonName`, `Location`, `filesize`) VALUES (1, 'Image1', 'FileLoc/1/image1.jpg', 1000), (2, 'Image1', 'FileLoc/2/image1.jpg', 5), (3, 'Image2', 'FileLoc/1/image2.jpg', 25000), (4, 'Image2', 'FileLoc/2/image2.jpg', 5000); -- -- -- Table structure for table `images` -- CREATE TABLE `images` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CommonName` varchar(255) NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `CommonName` (`CommonName`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Dumping data for table `images` -- INSERT INTO `images` (`ID`, `CommonName`) VALUES (1, 'Image1'), (2, 'Image2'); Hope this helps a bit. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext 101 Direct: 909-962-8547 __ CONFIDENTIALITY NOTICE: This communication, including attachments, is for the exclusive use of the person or entity to which it is addressed and may contain confidential, proprietary and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this by mistake, please contact the sender immediately. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Using Joins/Unions
No but there should be. If there's not my task is useless. Secondly yes. Unique name on it too. -- Ryan Coleman Publisher, d3photography.com ryan.cole...@cwis.biz m. 651.373.5015 o. 612.568.2749 On Aug 4, 2015, at 17:33, Wm Mussatto mussa...@csz.com wrote: On Tue, August 4, 2015 11:19, Ryan Coleman wrote: I have been a MySQL user and supporter for over a decade (since 2001) and I am almost ashamed to admit that I haven’t the faintest idea on how to do joins and unions. I have a specific query I would love to run… I have two tables, one with Unique data (“images”) and one with corresponding paths but many replicated records (“files”). I want to run a query that takes the results from /images/ and also searches /images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ in descending order returning just the first record (largest file size). There may be up to 750 records from /images/ and thusly could be 3000+ from /files/. How on earth do I do this? — Ryan First question, will there always be at least one record in the files table for every record in the images table? That controls the kind of join you will use. I don't think that a union is a player. Also, is there a unique record ID in each of the table? -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Using Joins/Unions
I have been a MySQL user and supporter for over a decade (since 2001) and I am almost ashamed to admit that I haven’t the faintest idea on how to do joins and unions. I have a specific query I would love to run… I have two tables, one with Unique data (“images”) and one with corresponding paths but many replicated records (“files”). I want to run a query that takes the results from /images/ and also searches /images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ in descending order returning just the first record (largest file size). There may be up to 750 records from /images/ and thusly could be 3000+ from /files/. How on earth do I do this? — Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Using Joins/Unions
On Tue, August 4, 2015 11:19, Ryan Coleman wrote: I have been a MySQL user and supporter for over a decade (since 2001) and I am almost ashamed to admit that I haven’t the faintest idea on how to do joins and unions. I have a specific query I would love to run… I have two tables, one with Unique data (“images”) and one with corresponding paths but many replicated records (“files”). I want to run a query that takes the results from /images/ and also searches /images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ in descending order returning just the first record (largest file size). There may be up to 750 records from /images/ and thusly could be 3000+ from /files/. How on earth do I do this? — Ryan First question, will there always be at least one record in the files table for every record in the images table? That controls the kind of join you will use. I don't think that a union is a player. Also, is there a unique record ID in each of the table? -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: optimizing UNIONs ?
On Tue, 10 Jun 2008, Martin wrote: Lucio So the net effect is to produce a cartesian join where ALL results from Query1 (are combined with) ALL results from Query2 In order to prevent cartesian join can you use a more narrowly defined predicate such as what is defined at http://www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries/ Thank you Martin for the suggestion to take the join out of the union. It is not exactly what suggestedin the URL you quote (that's more take the WHERE condition inside a subquery) but gave me an idea which I explain below In my case the WHERE condition is totally unpredictable (all the stuff below will actually be masked under a servlet which users will access to construct queries) Are you sure it is wise to create temptable considering MERGE would be disabled when temptable is specified? Apparently it was proven in the past that it was necessary to obtain a speed achievement in some conditions specific of my case. Molte Grazie Martin Prego ! (you are welcome) But why are you thanking me, while I should be thanking you ? :-) (this reminds me of the 8th scene of Petrolini's Nerone :-) http://www.drzap.it/O_Petrolini_Nerone.htm It's even on You Tube, google for Petrolini Nerone grazie) Now back to serious business a) when I create views of the form gct left join member1... left join member2... left join membern... where the member tables are real tables the queries are fast and use the indices on member1 ... membern b) if for maintenance purposes I create unions which concatenate three tables, say member1 (combo1) is union of tab1A tab1B tab1C member2 (combo2) is union of tab2A tab2B tab2C member3 (combo3) is union of tab3A tab3B tab3C while the union themselves are fast, the view above where each member is an union is 250 times slower ! c) it is not a matter of join optimization (STRAIGHT_JOIN does NOT help) d) I found no way to force the union to use the concatenation of the index. I tried index hints like (select * from tab1A force index (auxiliary)) union (select * from tab1B force index (auxiliary)) union (select * from tab1C force index (auxiliary)) but this has no effect. e) one obvious way out would be to store the unions into a physical table, so member1/2/3 will be physical tables. This is almost as fast as the single table query (scales with size) but has the disadvantage to waste disk space (and to require one remembers to update the physical union when one of the A B C components are updated f) the solution is to write the view as an union of joins (instead of a join of union views) create view xxx as (select .. gct left join tab1A.. left join tab2A.. left join tab3A..) union (select .. gct left join tab1B.. left join tab2B.. left join tab3B..) union (select .. gct left join tab1C.. left join tab2C.. left join tab3C..) This proves to be successful. Explain select returns a manageable query which uses the indices on the tabnX, and executes only 2.5 slower than the original query on a single table ... ... what's more important it remains fast even if one is accessing element in each of the three union chunks The updated notes on http://sax.iasf-milano.inaf.it/~lucio/temp/Temp/example.txt are probably clearer One can even dispense with the combo unions, everything is done on the fly from the physical tabnX. The only thing is that writing the definition of the union of joins is slightly painful (but can be automatized). -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html Multi pertransibunt et augebitur scientia Francis Bacon Instauratio Magna (http://tinyurl.com/2j3qk5) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizing UNIONs ?
(mysql 5.0.27 on SuSE Linux) I recently thought to use UNIONs to allow me to concatenate vertically some database tables with statements like this create or replace view combo as (select * from nov06) union (select *,ra_corr as ra_cor2,dec_corr as dec_cor2 from jul07) union (select *,ra_corr as ra_cor2,dec_corr as dec_cor2 from subaru where field2100); (a show create view tells me this was created as CREATE ALGORITHM=UNDEFINED, and of course gives the full list of columns) The sense of the above is that nov06 is a first release of an astronomical database containing a sky region, and the related catalogue has already been published (so it shall be mantained unchanged). jul07 and subaru are two incremental additions in other sky regions, which I'd like to see all together as an union ... and at the same time to keep physically separate to ease maintenance. The three tables in the union have the same layout, except that nov06 has two columns more. To allow the unions these columns are mimicked as identical copies of two other columns in the other two tables (per CREATE statement above). I do not expect the CREATE makes any difficulty ... it is shown as a full list of columns. All three tables have two indices, an UNIQUE one on two columns (`field`,`id`), and another one on the single column seq (a sequence number which is also auto_increment ... but the tables are static once created). In particular seq runs sequentially from table to table so that the first jul07.seq is equal to the last nov06.seq + 1 and so on. So far so good ... ... those unions work nicely, only slightly slower than a single table . In the past I had (and still have) also some views which allow simultaneous horizontal access to more than one single table via a glorified correlation table (just a table of pointers, I hope the definition below illustrates the usage clearly enough) create ALGORITHM=TEMPTABLE VIEW XLSS as list of column aliases from glorlss06 left join nov06 on glorlss06.nov06 =nov06.seq left join nov06b on glorlss06.nov06b =nov06b.seq left join nov06cd on glorlss06.nov06cd=nov06cd.seq ; This is just an example with three horizontal members. I have more complex examples with up to 30 members, and lived satisfactorily with them. (the ALGORITHM=TEMPTABLE and sometimes the usage of SELECT STRAIGHT_JOIN are the only tricks required to improve efficiency, the latter was even discussed on this list ... ah the glorlss06 of course have a couple of indices, an unique one on (`seq`,`nov06`) and another on nov06 alone. Just for reference this is example of EXPLAIN SELECT on such view explain select Xcatname,Xseq,Xra,Xdec from XLSS where Xseq=13121; ++-++--+---+---+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+---+-+-+--+-+ | 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL| 3385 | Using where | | 2 | DERIVED | glorlss06 | ALL | NULL | NULL | NULL | NULL| 3385 | | | 2 | DERIVED | nov06 | ref | auxiliary | auxiliary | 4 | lssdb.glorlss06.nov06 | 16 | | | 2 | DERIVED | nov06b | ref | auxiliary | auxiliary | 4 | lssdb.glorlss06.nov06b | 16 | | | 2 | DERIVED | nov06cd| ref | auxiliary | auxiliary | 4 | lssdb.glorlss06.nov06cd | 16 | | ++-++--+---+---+-+-+--+-+ In case this gets wrapped in the mail a copy can be seen at http://sax.iasf-milano.inaf.it/~lucio/temp/Temp/example.txt Such a statement (the select, not the explain) takes 0.19 seq . and now the trouble comes ... when I want to put everything together I create an horizontal view whose members are vertical unions create ALGORITHM=TEMPTABLE VIEW INTERIM as list of column aliases from glorcombo left join combo on glorcombo.combo =combo.seq left join combob on glorcombo.combob =combob.seq left join combocd on glorcombo.combocd=combocd.seq ; combo with its three members was illustrated above, and combob and combocd are fully equivalent unions with 3 members each. glorcombo is instead a physical table. A statement fully analogous to the previous one takes now 49 sec instead of a fraction. All the time is spent in the analysis phase of EXPLAIN select (which I report below, and, in case of wrap, at the URL given above explain select
4.1 and unions
Hi all, Is there anyone using version 4.1 that can verify or refute the claim posted at the bottom of the upgrade notes here? http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html The user claims that UNION statements with parentheses are broken in 4.1. That is, 'SELECT * FROM table1 UNION (SELECT * FROM table2)' or something similar. We're looking to upgrade but find te claim a bit dubious as there's no follow-up, no other mention, and I can't recall seeing anything on the list about it. Can anyone perform a quick test or verify one way or the other? I'd be much obliged. Thanks! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1 and unions
mysql select * from files where id 10 union (select * from files where id 20 and id 30); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 mysql select * from files where id 10 union select * from files where id 20 and id 30; ++-+-+---+---+---+--+ | id | pathref | version | mdate | ddate | size | md5 | ++-+-+---+---+---+--+ | 1 | 19 | 0 | 1094523040217 | NULL | 9 | NULL | | 2 | 21 | 0 | 1120090156726 | NULL |45 | NULL | | 3 | 22 | 0 | 1120089642527 | NULL |14 | NULL | | 4 | 23 | 0 | 1120089642527 | NULL |28 | NULL | | 5 | 25 | 0 | 1120781223174 | NULL | 6171 | NULL | | 6 | 26 | 0 | 1120781223204 | NULL | 5511 | NULL | | 7 | 31 | 0 | 1120781215964 | NULL | 13854 | NULL | | 8 | 33 | 0 | 1120781222173 | NULL | 6236 | NULL | | 9 | 35 | 0 | 1120781216184 | NULL | 15036 | NULL | | 21 | 48 | 0 | 1120781222753 | NULL | 27663 | NULL | | 22 | 49 | 0 | 1120781222723 | NULL | 6477 | NULL | | 23 | 50 | 0 | 112078103 | NULL | 6397 | NULL | | 24 | 51 | 0 | 1120781222663 | NULL | 12968 | NULL | | 25 | 52 | 0 | 1120781222713 | NULL | 8904 | NULL | | 26 | 53 | 0 | 1120781222693 | NULL | 6397 | NULL | | 27 | 54 | 0 | 1120781222673 | NULL | 6397 | NULL | | 28 | 55 | 0 | 1120781222653 | NULL | 6337 | NULL | | 29 | 56 | 0 | 1120781222623 | NULL | 6447 | NULL | ++-+-+---+---+---+--+ 18 rows in set (0.03 sec) Server version: 4.1.13-nt On Thu, 4 Aug 2005, Mike Johnson wrote: Hi all, Is there anyone using version 4.1 that can verify or refute the claim posted at the bottom of the upgrade notes here? http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html The user claims that UNION statements with parentheses are broken in 4.1. That is, 'SELECT * FROM table1 UNION (SELECT * FROM table2)' or something similar. We're looking to upgrade but find te claim a bit dubious as there's no follow-up, no other mention, and I can't recall seeing anything on the list about it. Can anyone perform a quick test or verify one way or the other? I'd be much obliged. Thanks! -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1 and unions
UNION is not broken; the syntax in the given comment is incorrect. You can do SELECT * FROM table UNION SELECT * FROM table or (SELECT * FROM table) UNION (SELECT * FROM table) but not SELECT * FROM table UNION (SELECT * FROM table) On 8/4/05, Mike Johnson [EMAIL PROTECTED] wrote: Hi all, Is there anyone using version 4.1 that can verify or refute the claim posted at the bottom of the upgrade notes here? http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html The user claims that UNION statements with parentheses are broken in 4.1. That is, 'SELECT * FROM table1 UNION (SELECT * FROM table2)' or something similar. We're looking to upgrade but find te claim a bit dubious as there's no follow-up, no other mention, and I can't recall seeing anything on the list about it. Can anyone perform a quick test or verify one way or the other? I'd be much obliged. Thanks! -- Mike Johnson Smarter Living, Inc. Web Developer www.smartertravel.com http://www.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1 and unions
From: Scott Noyes [mailto:[EMAIL PROTECTED] UNION is not broken; the syntax in the given comment is incorrect. You can do SELECT * FROM table UNION SELECT * FROM table or (SELECT * FROM table) UNION (SELECT * FROM table) but not SELECT * FROM table UNION (SELECT * FROM table) Oh, interesting. My next question was going to be if the second syntax is correct (all subqueries enclosed in parens), but looks like it's good. Thanks, Scott. -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNIONS 'Got error 12 from storage engine'
Memory problem. Error 12 = Cannot allocate memory Marc. -Message d'origine- De : Cliff Daniel [mailto:[EMAIL PROTECTED] Envoyé : jeudi 21 avril 2005 07:43 À : mysql@lists.mysql.com Objet : UNIONS 'Got error 12 from storage engine' Have a query that consists of 10 unions. For a period of time it will constantly return with an error 12. Moments later it might actually work once or twice. I can find nothing in any logs or whatever. The odd thing is that when it isn't working, simply reducing the unions to 8 or less returns with no problem. A) Very little to be read about on the net for Error 12 B) What little there is talks mostly of BDB, which is of no relevance. I've tried reproducing the problem with lots of unions, even tried joining some simple table to try to get the same error, but to no avail. I'm guess it is related to my specific schema. I'm on 4.1.11, up from 4.1.10a but that didn't fix it. Any debugging avenues suggested? I know someone is going to want an explain, and it'll have to wait until the morning. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIONS 'Got error 12 from storage engine'
Have a query that consists of 10 unions. For a period of time it will constantly return with an error 12. Moments later it might actually work once or twice. I can find nothing in any logs or whatever. The odd thing is that when it isn't working, simply reducing the unions to 8 or less returns with no problem. A) Very little to be read about on the net for Error 12 B) What little there is talks mostly of BDB, which is of no relevance. I've tried reproducing the problem with lots of unions, even tried joining some simple table to try to get the same error, but to no avail. I'm guess it is related to my specific schema. I'm on 4.1.11, up from 4.1.10a but that didn't fix it. Any debugging avenues suggested? I know someone is going to want an explain, and it'll have to wait until the morning. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unions will full column names won't work in 4.1.7
Not sure if this is a bug... probably should be. On 4.1.18 I can run: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY FOO.COL_A Which will work just fine However when I use this query on 4.1.7 I get ERROR 1250 (42000): Table 'ARTICLE' from one of the SELECTs cannot be used in global ORDER clause I have to rewrite it to use: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY COL_A .. see the change in the ORDER BY... I can't call if FOO.COL_A I have to call it COL_A Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unions will full column names won't work in 4.1.7
At 16:23 -0800 12/15/04, Kevin A. Burton wrote: Not sure if this is a bug... probably should be. On 4.1.18 I can run: 4.1.18? I assume you mean 4.0.18. Anyway, what you describe is according to the documentation: http://dev.mysql.com/doc/mysql/en/UNION.html (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY FOO.COL_A Which will work just fine However when I use this query on 4.1.7 I get ERROR 1250 (42000): Table 'ARTICLE' from one of the SELECTs cannot be used in global ORDER clause I have to rewrite it to use: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY COL_A .. see the change in the ORDER BY... I can't call if FOO.COL_A I have to call it COL_A Kevin -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unions will full column names won't work in 4.1.7
- Original Message - From: Kevin A. Burton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 7:23 PM Subject: unions will full column names won't work in 4.1.7 Not sure if this is a bug... probably should be. On 4.1.18 I can run: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY FOO.COL_A Which will work just fine However when I use this query on 4.1.7 I get ERROR 1250 (42000): Table 'ARTICLE' from one of the SELECTs cannot be used in global ORDER clause I have to rewrite it to use: (SELECT * FROM FOO WHERE ID = 1) UNION (SELECT * FROM FOO WHERE ID = 2) ORDER BY COL_A .. see the change in the ORDER BY... I can't call if FOO.COL_A I have to call it COL_A I'm not sure if I'd call that a bug either; you may want to check the manual to see if FOO.COL_A is *supposed* to work. Perhaps the bug is that it works in 4.0.18 when it shouldn't? I don't know if this helps but another way to do ORDER BY for UNIONs is to say Order by n where 'n' is an integer describing which column of the result set you are sorting on. Therefore, if you are sorting on the 3rd and 5th columns of the result set (both ascending), you'd say 'Order by 3, 5'. I'm not crazy about this approach myself - it seems likely that some users will scratch their heads and wonder what the '3' and '5' refer to - but it is certainly very concise and eliminates the issue of having a table name in the ORDER BY. Remember, the ORDER BY always refers to the result of the UNION, never to any of the individual SELECTs that make up the full query. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count Rows within unions
Hi SQL-Cracks How can I count the rows within a select with multiple unions? Count rows with one select is easy: select count(*) from table Count rows over multiple tables is complicated: select name from table1 union select name from table2 union select name from table3 order by name How can I count the rows over this 3 tables? Thank you in advance! Martin Rytz
[jp] Unions in mysql 3.22
CAN I USE UNION cluse in mysql 3.22?? thank you .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [jp] Unions in mysql 3.22
-Original Message- From: Jorge Paiva (f2) [mailto:[EMAIL PROTECTED] Sent: 18 November 2003 15:14 To: [EMAIL PROTECTED] Subject: [jp] Unions in mysql 3.22 CAN I USE UNION cluse in mysql 3.22?? No! UNION is implemented in MySQL 4.0.0 and later. - Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does mySQL support Pascal/C++ like Unions
Hello! Does mySQL support a thing like Pascal/C++ unions for attribute types, i.e. an attribute can be of two types at the same time. Thanks in advance. Florian
Convert with Unions
Hello all, I've tried searching through the lists but haven't been able to find an answer to my problem. If any one can help I would be very grateful, Thanks in advance. Anyway, on to the problem. I have two tables each with field that contains a date in string format 'YYMMDDhhmmss' I want to get the contents of those fields back as DateTime types. for example SELECT CONVERT(PatientDateTimeOfBirth, DATETIME) FROM orders returns the record set with the field as type DateTime. That works great, however when I try to union the results of two selects with the converts in them I get back the original string data not the date type. (simplified example) (SELECT CONVERT(PatientDateTimeOfBirth, DATETIME) FROM orders) UNION (SELECT CONVERT(PatientDateTimeOfBirth, DATETIME) FROM patients) This gives me two rows with the one field formatted like 'YYMMDDhhmmss' instead of the datetime type. Does anyone have any suggestions on what to try. Once again, Thanks in advance Andrew Taft -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Hi Kim, just send a reply a minute ago. I got it now. You want to change the way the data is presented at the screen and thats a different issue. You would have to make this in your apllication, not with mysql itself I believe. You basically want the DISTINCT function within the JOINS - but only applied for a certain column. Thats not possibe yet I believe. Best regards Nils Valentin Tokyo/Japan 2003 8 13 14:16Kim Kohen : G'day Nils Well, I can't speak for Daniel, and I'm not really up on his tables, but I'm not using left joins. The situation I have is not one where I need to display items that don't exist from one table or another. The data I get from a basic join is the _data_ I want - I just can't get it to display in a clear fashion. The join is forcing 'extra' superfluous data to be displayed. I know this could be eliminated if I could obtain all the data for a union, but I can't see a way to display as a union and query as a join. cheers kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
G'day Jim What about this? That's it, thanks! I'll be fiddling a bit to get a few other columns but so far this seems be be what I want. The only book I have which handles Unions is the MySQL Cookbook (Paul Dubois) and the examples didn't include any 'where' clauses so I didn't realise they could be used in Unions - dumb of me in retrospect. SELECT dummy.page_number,stories.story_name,stories.story_status FROM dummy,stories WHERE dummy.page_number = stories.page_number UNION SELECT dummy.page_number,dummy.ad_num,adtrack.ad_status FROM adtrack,dummy WHERE adtrack.ad_num = dummy.ad_num order by 1 ASC Cheers and thanks again - much appreciated kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Joins and Unions
Adtrack - holds data about an ads status (but NOT page number) Dummy - holds data about ad position, geometry and page number Stories - holds data about stories and their page number Pages - holds data about pages and their status I want to be able to display a page and all its associated pieces but without duplicates. I know a union would display what I want, but I can't get all the necessary data without doing a join. The current query is: SELECT dummy.page_number,dummy.ad_num,adtrack.ad_status,stories.story _name,stories. story_status FROM adtrack,dummy,stories WHERE adtrack.ad_num = dummy.ad_num AND dummy.page_number = stories.page_number order by dummy.page_number ASC What about this? SELECT dummy.page_number,stories.story_name,stories.story_status FROM dummy,stories WHERE dummy.page_number = stories.page_number UNION SELECT dummy.page_number,dummy.ad_num,adtrack.ad_status FROM adtrack,dummy WHERE adtrack.ad_num = dummy.ad_num order by 1 ASC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joins and Unions
G'day all, I'm pretty new to MySQL and very new to v4. This is probably going to sound strange, but is it possible to create a union using the logic of a join? I have 4 tables and I need to join data from them, but I'd like to display them like a Union. If I do a straight join, I get lots of multiple entries thus: PageAd_num status stories 14312-T1308 Ad Complete 1-marchingband-A33Placed 14312-T1308 Ad Complete 1-jobs-T33Placed 14312-T1308 Ad Complete 1-illegalbuild-A32 Placed 14312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 14312-T1308 Ad Complete 1-jobs-T33Corrected 14312-T1308 Ad Complete 1-car crash-T33 Placed Where ad_num 14312-T1308 Ad Complete is replicated to accommodate all the Joined data from the 'stories' table What I'd like to see is something like this: 14312-T1308 Ad Complete 11-marchingband-A33Placed 11-jobs-T33Placed 11-jobs-T33Corrected 11-illegalbuild-A32 Placed 11-haldonstfesty-T33 Placed 11-car crash-T33Placed Is this sort of thing possible? cheers kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
I have also had this trouble before if there are say 5 results from the second table, the first table of results would be duplicated 5 times on a join, i would also like to know how to get around this. G'day all, I'm pretty new to MySQL and very new to v4. This is probably going to sound strange, but is it possible to create a union using the logic of a join? I have 4 tables and I need to join data from them, but I'd like to display them like a Union. If I do a straight join, I get lots of multiple entries thus: PageAd_num status stories 14312-T1308 Ad Complete 1-marchingband-A33Placed 14312-T1308 Ad Complete 1-jobs-T33 Placed 14312-T1308 Ad Complete 1-illegalbuild-A32 Placed 14312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 14312-T1308 Ad Complete 1-jobs-T33 Corrected 14312-T1308 Ad Complete 1-car crash-T33 Placed Where ad_num 14312-T1308 Ad Complete is replicated to accommodate all the Joined data from the 'stories' table What I'd like to see is something like this: 14312-T1308 Ad Complete 11-marchingband-A33Placed 11-jobs-T33Placed 11-jobs-T33Corrected 11-illegalbuild-A32 Placed 11-haldonstfesty-T33 Placed 11-car crash-T33Placed Is this sort of thing possible? cheers kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
G'day Nils no offense, but are you actually using the JOIN syntax correctly ? No offence taken - that's why I'm asking the question - I don't know whether I'm approaching this correctly. As I said, I'm new to MySQL ... Could you post your query please ? OK. Adtrack - holds data about an ads status (but NOT page number) Dummy - holds data about ad position, geometry and page number Stories - holds data about stories and their page number Pages - holds data about pages and their status I want to be able to display a page and all its associated pieces but without duplicates. I know a union would display what I want, but I can't get all the necessary data without doing a join. The current query is: SELECT dummy.page_number,dummy.ad_num,adtrack.ad_status,stories.story_name,stories. story_status FROM adtrack,dummy,stories WHERE adtrack.ad_num = dummy.ad_num AND dummy.page_number = stories.page_number order by dummy.page_number ASC mysql SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 USING (id); I'm not familiar with either 'On' or 'Using' but I will take a look cheers kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
well i'd like to join tables without duplicates, currently i cannot do this unless i select the category table in a second loop to list all the categories for that particular row/record Hi Daniel and Kim, Isn't product_name product_category product_name product_category product_name product_category product_name product_category product_name product_category the way its supposed to be ? You are using the LEFT JOIN syntax, but actually using it just as a JOIN. You could also use something like SELECT * FROM products p, product_cat_join,product_category pc WHERE p.productID=pcj.productID AND pc.catID=pcj.catID; if I remember that correctly. This does exactly the same as the LEFT JOIN above. A LEFT JOIN is normally used to find records which exits in one table, BUT NOT in the other table. So for example: SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID WHERE pc.catID IS NULL Anybody: Please correct me if I talk rubbish here ;-) Best regards Nils Valentin Tokyo/Japan 2003年 8月 13日 水曜日 13:[EMAIL PROTECTED] さんは書きました: he is getting the same problem as me as i explained i use joins left join and inner join SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID; products productID product product_cat_join joinID productID catID product_category catID product_category so say there are 5 records of a product category against a product it would result in product_name product_category product_name product_category product_name product_category product_name product_category product_name product_category instead of product_name product_category product_category product_category product_category product_category Hi Kim, no offense, but are you actually using the JOIN syntax correctly ? Could you post your query please ? My guess is you are doing a JOIN without any set conditions. hat would give you the result as described (which is normal). You would use the WHERE clause or for a LEFT JOIN ...the ON clause to filter to correct data which you are requesting. Please have a look once more at the JOIN syntax: http://www.mysql.com/doc/en/JOIN.html Some examples: mysql SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 USING (id); Best regards Nils Valentin Tokyo/Japan 2003å¹´ 8æ 13æ¥ æ°´ææ¥ 09:25ãKim Kohen ããã¯æ¸ãã¾ãã: G'day all, I'm pretty new to MySQL and very new to v4. This is probably going to sound strange, but is it possible to create a union using the logic of a join? I have 4 tables and I need to join data from them, but I'd like to display them like a Union. If I do a straight join, I get lots of multiple entries thus: PageAd_num status stories 14312-T1308 Ad Complete 1-marchingband-A33Placed 1 4312-T1308 Ad Complete 1-jobs-T33Placed 1 4312-T1308 Ad Complete 1-illegalbuild-A32 Placed 1 4312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 1 4312-T1308 Ad Complete 1-jobs-T33 Corrected 14312-T1308 Ad Complete 1-car crash-T33 Placed Where ad_num 14312-T1308 Ad Complete is replicated to accommodate all the Joined data from the 'stories' table What I'd like to see is something like this: 14312-T1308 Ad Complete 11-marchingband-A33Placed 11-jobs-T33Placed 11-jobs-T33Corrected 11-illegalbuild-A32 Placed 11-haldonstfesty-T33 Placed 11-car crash-T33Placed Is this sort of thing possible? cheers kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
G'day Nils Well, I can't speak for Daniel, and I'm not really up on his tables, but I'm not using left joins. The situation I have is not one where I need to display items that don't exist from one table or another. The data I get from a basic join is the _data_ I want - I just can't get it to display in a clear fashion. The join is forcing 'extra' superfluous data to be displayed. I know this could be eliminated if I could obtain all the data for a union, but I can't see a way to display as a union and query as a join. cheers kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Kim Kohen wrote: Comparing PageAd_num status stories 14312-T1308 Ad Complete 1-marchingband-A33Placed 14312-T1308 Ad Complete 1-jobs-T33 Placed 14312-T1308 Ad Complete 1-illegalbuild-A32 Placed 14312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 14312-T1308 Ad Complete 1-jobs-T33 Corrected 14312-T1308 Ad Complete 1-car crash-T33 Placed to 14312-T1308 Ad Complete 11-marchingband-A33Placed 11-jobs-T33Placed 11-jobs-T33Corrected 11-illegalbuild-A32 Placed 11-haldonstfesty-T33 Placed 11-car crash-T33Placed it feels like you want your results grouped by Ad_num. http://www.mysql.com/doc/en/GROUP-BY-Functions.html Just my 2 cents, HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
he is getting the same problem as me as i explained i use joins left join and inner join SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID; products productID product product_cat_join joinID productID catID product_category catID product_category so say there are 5 records of a product category against a product it would result in product_name product_category product_name product_category product_name product_category product_name product_category product_name product_category instead of product_name product_category product_category product_category product_category product_category Hi Kim, no offense, but are you actually using the JOIN syntax correctly ? Could you post your query please ? My guess is you are doing a JOIN without any set conditions. hat would give you the result as described (which is normal). You would use the WHERE clause or for a LEFT JOIN ...the ON clause to filter to correct data which you are requesting. Please have a look once more at the JOIN syntax: http://www.mysql.com/doc/en/JOIN.html Some examples: mysql SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 USING (id); Best regards Nils Valentin Tokyo/Japan 2003年 8月 13日 水曜日 09:25、Kim Kohen さんは書きました: G'day all, I'm pretty new to MySQL and very new to v4. This is probably going to sound strange, but is it possible to create a union using the logic of a join? I have 4 tables and I need to join data from them, but I'd like to display them like a Union. If I do a straight join, I get lots of multiple entries thus: PageAd_num status stories 14312-T1308 Ad Complete 1-marchingband-A33Placed 1 4312-T1308 Ad Complete 1-jobs-T33Placed 1 4312-T1308 Ad Complete 1-illegalbuild-A32 Placed 1 4312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 1 4312-T1308 Ad Complete 1-jobs-T33Corrected 14312-T1308 Ad Complete 1-car crash-T33 Placed Where ad_num 14312-T1308 Ad Complete is replicated to accommodate all the Joined data from the 'stories' table What I'd like to see is something like this: 14312-T1308 Ad Complete 11-marchingband-A33Placed 11-jobs-T33Placed 11-jobs-T33Corrected 11-illegalbuild-A32 Placed 11-haldonstfesty-T33 Placed 11-car crash-T33Placed Is this sort of thing possible? cheers kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Hi Daniel and Kim, Isn't product_name product_category product_name product_category product_name product_category product_name product_category product_name product_category the way its supposed to be ? You are using the LEFT JOIN syntax, but actually using it just as a JOIN. You could also use something like SELECT * FROM products p, product_cat_join,product_category pc WHERE p.productID=pcj.productID AND pc.catID=pcj.catID; if I remember that correctly. This does exactly the same as the LEFT JOIN above. A LEFT JOIN is normally used to find records which exits in one table, BUT NOT in the other table. So for example: SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID WHERE pc.catID IS NULL Anybody: Please correct me if I talk rubbish here ;-) Best regards Nils Valentin Tokyo/Japan 2003 8 13 13:[EMAIL PROTECTED] : he is getting the same problem as me as i explained i use joins left join and inner join SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID; products productID product product_cat_join joinID productID catID product_category catID product_category so say there are 5 records of a product category against a product it would result in product_name product_category product_name product_category product_name product_category product_name product_category product_name product_category instead of product_name product_category product_category product_category product_category product_category Hi Kim, no offense, but are you actually using the JOIN syntax correctly ? Could you post your query please ? My guess is you are doing a JOIN without any set conditions. hat would give you the result as described (which is normal). You would use the WHERE clause or for a LEFT JOIN ...the ON clause to filter to correct data which you are requesting. Please have a look once more at the JOIN syntax: http://www.mysql.com/doc/en/JOIN.html Some examples: mysql SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 USING (id); Best regards Nils Valentin Tokyo/Japan 2003 8 13 09:25Kim Kohen : G'day all, I'm pretty new to MySQL and very new to v4. This is probably going to sound strange, but is it possible to create a union using the logic of a join? I have 4 tables and I need to join data from them, but I'd like to display them like a Union. If I do a straight join, I get lots of multiple entries thus: PageAd_num status stories 14312-T1308 Ad Complete 1-marchingband-A33Placed 1 4312-T1308 Ad Complete 1-jobs-T33Placed 1 4312-T1308 Ad Complete 1-illegalbuild-A32 Placed 1 4312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 1 4312-T1308 Ad Complete 1-jobs-T33Corrected 14312-T1308 Ad Complete 1-car crash-T33 Placed Where ad_num 14312-T1308 Ad Complete is replicated to accommodate all the Joined data from the 'stories' table What I'd like to see is something like this: 14312-T1308 Ad Complete 11-marchingband-A33Placed 11-jobs-T33Placed 11-jobs-T33Corrected 11-illegalbuild-A32 Placed 11-haldonstfesty-T33 Placed 11-car crash-T33Placed Is this sort of thing possible? cheers kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Hi Daniel, I am still not sure what exactly you are trying to achieve. If you specify the WHERE condition correctly (or ON or USING for the LEFT JOIN) then there is no duplicate entry. I believe what you are asking has more to do with the way how to present the data in the output. Best regards Nils Valentin Tokyo/Japan 2003 8 13 14:[EMAIL PROTECTED] : well i'd like to join tables without duplicates, currently i cannot do this unless i select the category table in a second loop to list all the categories for that particular row/record Hi Daniel and Kim, Isn't product_name product_category product_name product_category product_name product_category product_name product_category product_name product_category the way its supposed to be ? You are using the LEFT JOIN syntax, but actually using it just as a JOIN. You could also use something like SELECT * FROM products p, product_cat_join,product_category pc WHERE p.productID=pcj.productID AND pc.catID=pcj.catID; if I remember that correctly. This does exactly the same as the LEFT JOIN above. A LEFT JOIN is normally used to find records which exits in one table, BUT NOT in the other table. So for example: SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID WHERE pc.catID IS NULL Anybody: Please correct me if I talk rubbish here ;-) Best regards Nils Valentin Tokyo/Japan 2003 8 13 13:[EMAIL PROTECTED] : he is getting the same problem as me as i explained i use joins left join and inner join SELECT * FROM products p LEFT JOIN product_cat_join pcj ON p.productID=pcj.productID LEFT JOIN product_category pc ON pc.catID=pcj.catID; products productID product product_cat_join joinID productID catID product_category catID product_category so say there are 5 records of a product category against a product it would result in product_name product_category product_name product_category product_name product_category product_name product_category product_name product_category instead of product_name product_category product_category product_category product_category product_category Hi Kim, no offense, but are you actually using the JOIN syntax correctly ? Could you post your query please ? My guess is you are doing a JOIN without any set conditions. hat would give you the result as described (which is normal). You would use the WHERE clause or for a LEFT JOIN ...the ON clause to filter to correct data which you are requesting. Please have a look once more at the JOIN syntax: http://www.mysql.com/doc/en/JOIN.html Some examples: mysql SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 USING (id); Best regards Nils Valentin Tokyo/Japan 2003 8 13 09:25Kim Kohen : G'day all, I'm pretty new to MySQL and very new to v4. This is probably going to sound strange, but is it possible to create a union using the logic of a join? I have 4 tables and I need to join data from them, but I'd like to display them like a Union. If I do a straight join, I get lots of multiple entries thus: PageAd_num status stories 14312-T1308 Ad Complete 1-marchingband-A33Placed 1 4312-T1308 Ad Complete 1-jobs-T33Placed 1 4312-T1308 Ad Complete 1-illegalbuild-A32 Placed 1 4312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 1 4312-T1308 Ad Complete 1-jobs-T33 Corrected 14312-T1308 Ad Complete 1-car crash-T33 Placed Where ad_num 14312-T1308 Ad Complete is replicated to accommodate all the Joined data from the 'stories' table What I'd like to see is something like this: 14312-T1308 Ad Complete 11-marchingband-A33Placed 11-jobs-T33Placed 11-jobs-T33Corrected 11-illegalbuild-A32 Placed 11-haldonstfesty-T33 Placed 11-car crash-T33Placed Is this sort of thing possible? cheers kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL:
Re: Joins and Unions
G'day Nils just send a reply a minute ago. I got it now. You want to change the way the data is presented at the screen and thats a different issue. You would have to make this in your apllication, not with mysql itself I believe. You basically want the DISTINCT function within the JOINS - but only applied for a certain column. Thats not possibe yet I believe. Ah, ok, now where getting somewhere:) I had already tried Distinct but as you say, it can't really work with the joins I have currently. I'm looking to see if it's possible to simply set the displayed columns (I'm using PHP) of the redundant stuff to . Which leads me to another question. I've never used temporary tables. Would it be possible for me to do a join and form a temporary table from it? If that's possible, I could get all the data I need for a basic Union and I'd be happy - I think:) cheers kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Hi Kim, You can create a temporary table by using a table alias. The temporary table will only be accessable by the current connection. Any other connection will not be able to use it. If you want to make a table which can be used by several connections (f.e for a join) you could create a HEAP table which remains completely in the memory, but all data goes when the server crashes or powered off, the structure remains. Best regards Nils Valentin Tokyo/Japan 2003 8 13 15:21: G'day Nils just send a reply a minute ago. I got it now. You want to change the way the data is presented at the screen and thats a different issue. You would have to make this in your apllication, not with mysql itself I believe. You basically want the DISTINCT function within the JOINS - but only applied for a certain column. Thats not possibe yet I believe. Ah, ok, now where getting somewhere:) I had already tried Distinct but as you say, it can't really work with the joins I have currently. I'm looking to see if it's possible to simply set the displayed columns (I'm using PHP) of the redundant stuff to . Which leads me to another question. I've never used temporary tables. Would it be possible for me to do a join and form a temporary table from it? If that's possible, I could get all the data I need for a basic Union and I'd be happy - I think:) cheers Kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
how can it be confusing ? nobody ever experienced it before? simply when you join 2 tables together if you get 5 records from the second table the first table will display 5 times aswell Hi Daniel, I am still not sure what exactly you are trying to achieve. If you specify the WHERE condition correctly (or ON or USING for the LEFT JOIN) then there is no duplicate entry. I believe what you are asking has more to do with the way how to present the data in the output. Best regards Nils Valentin Tokyo/Japan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins and Unions
Hi Kim, no offense, but are you actually using the JOIN syntax correctly ? Could you post your query please ? My guess is you are doing a JOIN without any set conditions. hat would give you the result as described (which is normal). You would use the WHERE clause or for a LEFT JOIN ...the ON clause to filter to correct data which you are requesting. Please have a look once more at the JOIN syntax: http://www.mysql.com/doc/en/JOIN.html Some examples: mysql SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql SELECT * FROM table1 LEFT JOIN table2 USING (id); Best regards Nils Valentin Tokyo/Japan 2003 8 13 09:25Kim Kohen : G'day all, I'm pretty new to MySQL and very new to v4. This is probably going to sound strange, but is it possible to create a union using the logic of a join? I have 4 tables and I need to join data from them, but I'd like to display them like a Union. If I do a straight join, I get lots of multiple entries thus: PageAd_num status stories 14312-T1308 Ad Complete 1-marchingband-A33Placed 14312-T1308 Ad Complete 1-jobs-T33Placed 14312-T1308 Ad Complete 1-illegalbuild-A32 Placed 14312-T1308 Ad Complete 1-haldonstfesty-T33 Placed 14312-T1308 Ad Complete 1-jobs-T33Corrected 14312-T1308 Ad Complete 1-car crash-T33 Placed Where ad_num 14312-T1308 Ad Complete is replicated to accommodate all the Joined data from the 'stories' table What I'd like to see is something like this: 14312-T1308 Ad Complete 11-marchingband-A33Placed 11-jobs-T33Placed 11-jobs-T33Corrected 11-illegalbuild-A32 Placed 11-haldonstfesty-T33 Placed 11-car crash-T33Placed Is this sort of thing possible? cheers kim -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unions
Does anyone else feel that lack of support for subqueries is the number 1 deficiency in MySQL? I'm not sure why support for subqueries is being Actually, I am much more eagerly waiting for views and Unicode support that sub-selects, although of course these would be very nice too. Vaclav Dvorak [EMAIL PROTECTED] IDAS, s.r.o.http://www.idas.cz - 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: Unions
Yes, I agree sub-queries should be the highest priority with the next release judging from the e-mails going around at the moment. This was the first problem I found in my first day using MySQL. I don't understand with all the versions that have been released of MySQL since 1996 why they neglected one of the most fundamental SQL capabilities. There must be a good reason !!! ??? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 27 June 2002 17:10 To: [EMAIL PROTECTED] Subject: RE: Unions Does anyone else feel that lack of support for subqueries is the number 1 deficiency in MySQL? I'm not sure why support for subqueries is being Actually, I am much more eagerly waiting for views and Unicode support that sub-selects, although of course these would be very nice too. Vaclav Dvorak [EMAIL PROTECTED] IDAS, s.r.o.http://www.idas.cz - 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: Unions
[EMAIL PROTECTED] wrote: Does anyone else feel that lack of support for subqueries is the number 1 deficiency in MySQL? I'm not sure why support for subqueries is being Actually, I am much more eagerly waiting for views and Unicode support that sub-selects, although of course these would be very nice too. Unicode support will be available in MySQL-4.1 -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-902-856-80-21 - 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: Unions
Hiho hiho! How about: CREATE TABLE tmp_whatever SELECT list FROM table1... INSER INTO tmp_whatever SELECT list FROM table2... ? Greetings Ralf P.S.: A bit dirty for someone who got used to Oracle ;-) Arul wrote: Hi Can i use Unions in MySQL. If not could anyone comeout with some alternates.. Regards, -Arul -- Ralf Narozny Splendid Internet GmbH Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: Unions
Does anyone else feel that lack of support for subqueries is the number 1 deficiency in MySQL? I'm not sure why support for subqueries is being deferred for so long. Certainly, uncorrelated subqueries are almost trivial to implement and the truth is that correlated subqueries aren't really all that difficult either (I implemented both for a proprietary query engine in a past life). Even a naive suboptimal implementation would make life *so* much easier. ~chuck -Original Message- From: Arul [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 26, 2002 12:33 AM To: MySQL; Paul DuBois Subject: Re: Unions But MYSQL4.0 is currently in its Alpha ... Any idea when the Release of 4.0 would be made.. Also any idea about 4.1 Release reg when it would be coz i am eagerly expecting for subqueries in MYSQL -Arul - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Arul [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Wednesday, June 26, 2002 10:00 AM Subject: Re: Unions At 9:47 +0530 6/26/02, Arul wrote: Hi Can i use Unions in MySQL. As of MySQL 4. If not could anyone comeout with some alternates.. Regards, -Arul sql,query - 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 IMPORTANT NOTICES: This message is intended only for the addressee. Please notify the sender by email if you are not the intended recipient. If you are not the intended recipient, you may not copy, disclose, or distribute this message or its content to any other person and any such actions may be unlawful. Electronic mail sent through the Internet is not secure. WR Hambrecht + Co (WRH+Co) does not accept time sensitive, action-oriented messages or transaction orders, including orders to purchase or sell securities, via email. WRH+Co reserves the right to monitor and review the content of all messages sent to or from this email address. Messages sent to or from this email address may be stored on the WRH+Co email system. - 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: Unions
* Arul Can i use Unions in MySQL. From version 4 you can: URL: http://www.mysql.com/doc/U/N/UNION.html If not could anyone comeout with some alternates.. The normal workaround is to use temporary tables: create temporary table t1 select ...; insert into t1 select ...; select * from t1; -- Roger - 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: Unions
Take a look at http://www.mysql.com/doc/;. If you search the keyword union you can get an answer. Arul wrote: Hi Can i use Unions in MySQL. If not could anyone comeout with some alternates.. Regards, -Arul sql, - 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 -- Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ - 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
Unions
Hi All Is there anything Equivalent in MYSQL for Union in Oracle.. As per my understanding mysql doesnt support unions ...so any suggestions what could be done.. -Arul - 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: Unions
Arul, Saturday, June 01, 2002, 9:26:48 AM, you wrote: A Is there anything Equivalent in MYSQL for Union in Oracle.. A As per my understanding mysql doesnt support unions ...so any suggestions A what could be done.. MySQL supports UNION since 4.0.0. Look at: http://www.mysql.com/doc/U/N/UNION.html A -Arul -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: Unions
They are bugged in 4.0.1 (apparently fixed in 4.0.2) so I wouldn't rely on them too much. Richard - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, June 01, 2002 11:33 AM Subject: Re: Unions Arul, Saturday, June 01, 2002, 9:26:48 AM, you wrote: A Is there anything Equivalent in MYSQL for Union in Oracle.. A As per my understanding mysql doesnt support unions ...so any suggestions A what could be done.. MySQL supports UNION since 4.0.0. Look at: http://www.mysql.com/doc/U/N/UNION.html A -Arul -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
query unions..
I have a table: CREATE TABLE vote_count ( id int(10) DEFAULT '0' NOT NULL auto_increment, imageid int(6) DEFAULT '0' NOT NULL, catid int(4) DEFAULT '0' NOT NULL, count int(6) DEFAULT '0' NOT NULL, PRIMARY KEY (id), INDEX (imageid, catid) ); and am trying to query for all image id's that are in an intersection of catid's I supply and are a difference from all of the union'd catid's I also supply.. SELECT vc0.imageid, vc4.catid, vc5.catid FROM vote_count AS vc0 LEFT JOIN vote_count AS vc1 ON vc0.imageid = vc1.imageid LEFT JOIN vote_count AS vc2 ON vc0.imageid = vc2.imageid LEFT JOIN vote_count AS vc3 ON vc0.imageid = vc3.imageid LEFT JOIN vote_count AS vc4 ON vc0.imageid = vc4.imageid LEFT JOIN vote_count AS vc5 ON vc0.imageid = vc5.imageid WHERE vc0.catid='15' AND vc1.catid='37' AND vc2.catid='52' AND vc3.catid='10' AND NOT ( vc4.catid = '30' OR vc4.catid = '1' OR vc5.catid = '1' OR vc5.catid = '30' ) GROUP BY vc0.imageid ORDER BY vc0.count + vc1.count + vc2.count + vc3.count DESC; This and everything else I've tried so far doesn't seem to work. The first intersection I can make work without problem but then making a union of the other catid's and finding the difference seems to mess up. The problem seems to be that while I can strip individual rows from the results the imageid still gets through because the catid is only linked once per image per row. I also tried this with similar poor results: SELECT vc0.imageid, vc0.catid, vc1.catid, vc2.catid, vc3.catid, vcneg.catid FROM vote_count AS vc0 LEFT JOIN vote_count AS vc1 ON vc0.imageid = vc1.imageid LEFT JOIN vote_count AS vc2 ON vc0.imageid = vc2.imageid LEFT JOIN vote_count AS vc3 ON vc0.imageid = vc3.imageid LEFT JOIN vote_count AS vcneg ON vc0.imageid = vcneg.imageid WHERE vc0.catid='15' AND vc1.catid='37' AND vc2.catid='52' AND vc3.catid='10' AND ( vcneg.catid != '30' OR vcneg.catid != '1' ) GROUP BY vc0.imageid ORDER BY vc0.count + vc1.count + vc2.count + vc3.count DESC; Can anyone tell me how to do this the right way? Thanks. - 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
intersections, unions, and differences
I have two lists of queries. One returns the intersection of all the queries within itself.. the actual intersection being done by an external function (which I'd like to change if I can learn how), the other the union of all queries within itself.. again using an external function for the union.. and then an external function does a difference between the intersection and the union to get the final result. Would it be even possible to code something like that as just a single query? Right now it is using something like 60 queries to get the result and that seems like I must be doing it wrong even though it responds quickly. All the best people in life seem to like LINUX. --Steve Wozniak *^*^*^* Michael McGlothlin [EMAIL PROTECTED] http://mlug.missouri.edu/~mogmios/projects/ - 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
UNIONS in MySQL 4.0
HI! I am waiting on the official launch of 4 and want to know more about the union tables. Can someone give me the low down on this? I know I will need it (I have to combine 15 tables into one big pile of data) so please help. Thanx! ** - Deryck Henson - http://www.comp-u-exchange.com ** - 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: Sorry, no need to answer question about unions...
On Fri, Jul 13, 2001 at 08:44:54PM -0700, William M. Shubert wrote: Ugh, I couldn't find the mailing list archives until I looked at the bottom of my own message that got mailed back to me! I searched, found out about the temporary table workaround for no unions, sorry for bothering you all with a question that had been answered before. I'm still eager for MySql to have real unions so I can simplify my sql code, but this looks good enough for now! Code for UNIONs has started to appear in the MySQL 4.0 tree, so hope is in sight. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 28 days, processed 213,541,496 queries (88/sec. avg) - 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
