Re: Fwd: Re: Re: [Bug in UNION clause]
| San Francisco | USA | San Francisco | CA| CA| http://www.innsf.com | FULL | | 95.00 | 265.00 |1 | Authentic 1872 Italianate Victorian Mansion. Rich Victorian decor. English Garden with Redwood Hot Tub. Sundeck with spectacular SF vistas. Excellent restaurants nearby. Be welcomed as a friend.| yes | no | no | 1942 | y|| y | y| y | | | y| | 1949 | FULL | The Monte Cristo | San Francisco | USA | San Francisco | CA| CA| http://www.virtualcities.com/ons/ca/b/cab3504.htm | FULL | | 83.00 | 118.00 |1 | 1875 hotel-saloon-bordello furnished with antiques. Each room uniquely decorated: Georgian four-poster, Chinese wedding bed, spindle bed. Personal service. | yes | no | yes | 1949 | || | y| y | | | | | 1960 | FULL | Victorian Inn on the Park | San Francisco | USA | San Francisco | CA| CA| http://www.victorianinnonthepark.com | FULL | | 159.00 | 199.00 |1 | 1897 Queen Anne Victorian near Golden Gate Park, downtown. Each room has antiques, flowers, beautiful comforters, down pillows, phones. | yes | no | no | 1960 | y|| y | y| | | y | y| | 7954 | FULL | The Hensley House | San Jose | USA | San Francisco | CA| CA| http://www.hensleyhouse.com | FULL | | 150.00 | 325.00 |1 | The Hensley House is a 3 story Victorian and a two story craftsman style home. Each house is decorated in antiques relative to the period. | ltd | no | no | 7954 | y|| y | y| | | y | y| | 2193 | FULL | Gerstle Park Inn | San Rafael| USA | San Francisco | CA| CA| http://www.gerstleparkinn.com | FULL | | 179.00 | 245.00 |1 | All rooms have private baths, patio or decks. All are furnished with rich fabrics and beautiful antiques. King suites with Jacuzzi tub. | yes | no | ltd | 2193 | y|| y | y| | | | y| | 12884 | FULL | Gables Inn-Sausalito, The | Sausalito | USA | San Francisco | CA| CA| http://www.gablesinnsausalito.com | FULL | | 135.00 | 325.00 |1 | Originally built in 1869, the inn has recently been completely renovated into a cozy BB offering 9 distinctive suites artfully decorated. Just 5 minutes north of the Golden Gate Bridge. | yes | no | yes | 12884 | y || y | | y | | y | y| again, having some duplicates listed twice that should not be there. - Original Message - From: Sinisa Milivojevic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 12, 2002 10:27 AM Subject: Re: Fwd: Re: Re: [Bug in UNION clause] You seem to have missed my mail. I sent it to you and to bugs@... Both of your SELECT's return empty resust set. The UNION also returns empty result set. I still have your tables, though ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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: Fwd: Re: Re: [Bug in UNION clause]
Please, re-send me: * mysqldump of the tables * the exact UNION query that you ran -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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: [Bug in UNION clause]
Here is the dump of those tables being used:
allusa:
+--++--+-+--+---
+
| Field| Type | Null | Key | Default |
Extra |
+--++--+-+--+---
+
| id | int(10) unsigned | | PRI | 0|
|
| line | enum('FULL','LINE','NSHW') | | MUL | FULL |
|
| name | varchar(50)| | MUL | |
|
| address | varchar(50)| YES | | NULL |
|
| paddress | varchar(50)| YES | | NULL |
|
| city | varchar(50)| | MUL | |
|
| listcity | varchar(50)| | MUL | |
|
| state| varchar(4) | | MUL | |
|
| liststate| varchar(4) | | MUL | |
|
| statename| varchar(20)| | MUL | |
|
| zip | varchar(10)| YES | | NULL |
|
| country | enum('','USA','Canada')| | MUL | |
|
| phone| varchar(20)| YES | | NULL |
|
| rs | varchar(20)| YES | | NULL |
|
| fax | varchar(20)| YES | | NULL |
|
| email| varchar(100) | YES | | NULL |
|
| url | varchar(150) | YES | | NULL |
|
| innkeeper| varchar(50)| YES | | NULL |
|
| season | varchar(24)| YES | | All Year |
|
| cost_range_low | decimal(8,2) | | | 0.00 |
|
| cost_range_high | decimal(8,2) | | | 0.00 |
|
| rooms| smallint(5) unsigned | YES | | NULL |
|
| pb | smallint(5) unsigned | YES | | NULL |
|
| visa | enum('Visa') | YES | | NULL |
|
| mc | enum('MC') | YES | | NULL |
|
| amex | enum('AmEx') | YES | | NULL |
|
| disc | enum('Disc') | YES | | NULL |
|
| mostcc | enum('Most CC')| YES | | NULL |
|
| rated| enum('y') | YES | | NULL |
|
| tac | char(2)| YES | | NULL |
|
| child| enum('yes','no','ltd') | YES | | NULL |
|
| smoke| enum('yes','no','ltd') | YES | | NULL |
|
| pets | enum('yes','no','ltd') | YES | | NULL |
|
| wc | enum('yes','no','ltd') | YES | | NULL |
|
| bb_code | enum('AP','BB','EP','MAP') | YES | | NULL |
|
| language | varchar(100) | YES | | NULL |
|
| breakfast| varchar(35)| YES | | NULL |
|
| otherfood| varchar(50)| YES | | NULL |
|
| amenities| varchar(76)| YES | | NULL |
|
| attraction | text | YES | | NULL |
|
| desc1| varchar(255) | YES | | NULL |
|
| ex_desc | text | YES | | NULL |
|
| room1desc| varchar(255) | YES | | NULL |
|
| room2desc| varchar(255) | YES | | NULL |
|
| ongoing_specials | varchar(255) | YES | | NULL |
|
| airport | varchar(40)| YES | | NULL |
|
| bookable | varchar(10)| YES | | NULL |
|
| latitude | float(10,6)| | MUL | 0.00 |
|
| longitude| float(10,6)| | | 0.00 |
|
| mat_type | tinyint(3) unsigned| YES | | NULL |
|
| keywords | varchar(100) | | | |
|
| region1 | tinyint(3) unsigned| YES | | 0|
|
| region2 | tinyint(3) unsigned| YES | | 0|
|
| last_updated | timestamp(14) | YES | | NULL |
|
| showmap | tinyint(1) | YES | | 1|
|
| showlink | tinyint(1) unsigned| YES | | 0|
|
| updated_by | char(1)| YES | | NULL |
|
| comments | varchar(200) | YES | | NULL |
|
| coupons | enum('yes','no') | | | no |
|
| reservations | enum('yes','no') | YES | | NULL |
|
| newsletter | enum('yes','no') | YES | | NULL |
|
Re: [Bug in UNION clause]
cliff, Thursday, August 01, 2002, 11:46:28 PM, you wrote: Description: c The UNION clause displays results 2 times even when not using the c ALL keyword with it. example: (SELECT c allusa.id,line,name,city,country,listcity,state,liststate,url,line,bookable,cost_range_low,cost_range_high,showlink,desc1,child,pets,wc,spec.id,spec.historic,spec.skiing,spec.romance,spec.antiques,spec.family_fun,spec.spas,spec.luxury_,spec.historic c FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id WHERE city = 'san francisco' AND line=1 order by state,city,name) UNION (SELECT c allusa.id,line,name,city,country,listcity,state,liststate,url,line,bookable,cost_range_low,cost_range_high,showlink,desc1,child,pets,wc,spec.id,spec.historic,spec.skiing,spec.romance,spec.antiques,spec.family_fun,spec.spas,spec.luxury_,spec.historic c FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id WHERE (line=1) AND (city LIKE '%san%' OR listcity LIKE '%san%' OR state='san' OR statename LIKE '%san%' OR country LIKE '%san%' OR name LIKE c '%san%') AND (city LIKE '%francisco%' OR listcity LIKE '%francisco%' OR state='francisco' OR statename LIKE '%francisco%' OR country LIKE '%francisco%' OR name LIKE '%francisco%')ORDER BY c state,city,name ) LIMIT 15,15 c produces the city San Francisco twice in the listing. How-To-Repeat: c http://www.lanierbb.com/search/search.php?pt=bbq=san+franciscostart=15numr=29 Can you send a dump of your tables for testing? -- 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
