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.000000 |
|
| longitude | float(10,6) | | | 0.000000 |
|
| 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 |
|
+------------------+----------------------------+------+-----+----------+---
----+
spec:
+-------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | | PRI | 0 | |
| antiques | char(1) | YES | | NULL | |
| architecture | char(1) | YES | | NULL | |
| comfort | char(1) | YES | | NULL | |
| conference | char(1) | YES | | NULL | |
| decor | char(1) | YES | | NULL | |
| diet_vegetarian | char(1) | YES | | NULL | |
| family_fun | char(1) | YES | | NULL | |
| farm_vacation | char(1) | YES | | NULL | |
| fishing | char(1) | YES | | NULL | |
| gardens | char(1) | YES | | NULL | |
| golf | char(1) | YES | | NULL | |
| gourmet | char(1) | YES | | NULL | |
| historic | char(1) | YES | | NULL | |
| low_price | char(1) | YES | | NULL | |
| nature | char(1) | YES | | NULL | |
| romance | char(1) | YES | | NULL | |
| skiing | char(1) | YES | | NULL | |
| spas | char(1) | YES | | NULL | |
| sports | char(1) | YES | | NULL | |
| weddings_reunions | char(1) | YES | | NULL | |
| luxury_ | char(1) | YES | | NULL | |
| green | char(1) | YES | | NULL | |
| special_ | char(1) | YES | | NULL | |
| updated_by | char(1) | YES | | NULL | |
+-------------------+------------------+------+-----+---------+-------+
here is the output when I run that same command in mysql:
*************************** 1. row ***************************
id: 1967
line: FULL
name: The Inn
city: San Francisco
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.thebandb.com
line: FULL
bookable:
cost_range_low: 80.00
cost_range_high: 420.00
showlink: 1
desc1:
child:
pets:
wc:
id: 1967
historic: NULL
skiing: NULL
romance: NULL
antiques: NULL
family_fun: NULL
spas: NULL
luxury_: NULL
historic: NULL
*************************** 2. row ***************************
id: 1934
line: FULL
name: The Inn at Union Square
city: San Francisco
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.unionsquare.com
line: FULL
bookable:
cost_range_low: 195.00
cost_range_high: 375.00
showlink: 1
desc1: An elegant, cozy European-style hotel in the heart of San
Francisco. A half-block from Union Square, & close to the finest
restaurants, best shopping & wonderful theater. Our location in unbeatable!
child: ltd
pets: no
wc: yes
id: 1934
historic:
skiing:
romance:
antiques:
family_fun:
spas:
luxury_: y
historic:
*************************** 3. row ***************************
id: 1942
line: FULL
name: The Inn San Francisco
city: San Francisco
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.innsf.com
line: FULL
bookable:
cost_range_low: 95.00
cost_range_high: 265.00
showlink: 1
desc1: 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.
child: yes
pets: no
wc: no
id: 1942
historic: y
skiing:
romance: y
antiques: y
family_fun: y
spas:
luxury_:
historic: y
*************************** 4. row ***************************
id: 1949
line: FULL
name: The Monte Cristo
city: San Francisco
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.virtualcities.com/ons/ca/b/cab3504.htm
line: FULL
bookable:
cost_range_low: 83.00
cost_range_high: 118.00
showlink: 1
desc1: 1875 hotel-saloon-bordello furnished with antiques. Each
room uniquely decorated: Georgian four-poster, Chinese wedding bed, spindle
bed. Personal service.
child: yes
pets: no
wc: yes
id: 1949
historic:
skiing:
romance:
antiques: y
family_fun: y
spas:
luxury_:
historic:
*************************** 5. row ***************************
id: 1960
line: FULL
name: Victorian Inn on the Park
city: San Francisco
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.victorianinnonthepark.com
line: FULL
bookable:
cost_range_low: 159.00
cost_range_high: 199.00
showlink: 1
desc1: 1897 Queen Anne Victorian near Golden Gate Park, downtown.
Each room has antiques, flowers, beautiful comforters, down pillows, phones.
child: yes
pets: no
wc: no
id: 1960
historic: y
skiing:
romance: y
antiques: y
family_fun:
spas:
luxury_: y
historic: y
*************************** 1. row ***************************
id: 7498
line: FULL
name: Webster House B&B Inn
city: Alameda
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://websterhouse.home.attbi.com
line: FULL
bookable:
cost_range_low: 95.00
cost_range_high: 175.00
showlink: 1
desc1: Oldest house on Island in San Francisco Bay, 22nd City
Historical Monument built in New York and shipped around Cape Horn in 1854.
child: yes
pets: no
wc: no
id: 7498
historic: y
skiing:
romance: y
antiques: y
family_fun: y
spas:
luxury_: y
historic: y
*************************** 2. row ***************************
id: 2268
line: FULL
name: The Holbrooke Hotel
city: Grass Valley
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.holbrooke.com
line: FULL
bookable:
cost_range_low: 0.00
cost_range_high: 0.00
showlink: 1
desc1: Located in the the Heart of the Gold Country, the Holbrooke
Hotel is reminiscent of California's glorious past.
child: yes
pets: no
wc: yes
id: 2268
historic: y
skiing:
romance: y
antiques: y
family_fun:
spas:
luxury_:
historic: y
*************************** 3. row ***************************
id: 4119
line: FULL
name: Dancing Coyote Beach
city: Inverness
country: USA
listcity: San Francisco
state: CA
liststate: CA
url:
line: FULL
bookable:
cost_range_low: 110.00
cost_range_high: 175.00
showlink: 0
desc1: Four lovely guest cottages nestled among the pines on the
shore of Tomales Bay. Secluded, yet a three-minute walk to restaurants.
child:
pets: yes
wc:
id: 4119
historic:
skiing:
romance:
antiques:
family_fun:
spas:
luxury_:
historic:
*************************** 4. row ***************************
id: 2057
line: FULL
name: Dockside Boat & Bed
city: Oakland
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.boatandbed.com
line: FULL
bookable:
cost_range_low: 125.00
cost_range_high: 400.00
showlink: 1
desc1: Intimate accommodations on private sail, motor yachts.
Private charters & romantic candlelight catered dinners available. Enjoy SF
skyline from the deck of your own private yacht.
child: ltd
pets: no
wc: no
id: 2057
historic:
skiing:
romance: y
antiques:
family_fun:
spas:
luxury_:
historic:
*************************** 5. row ***************************
id: 8647
line: FULL
name: 24 Henry Guesthouse
city: San Francisco
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.24Henry.com
line: FULL
bookable:
cost_range_low: 65.00
cost_range_high: 109.00
showlink: 1
desc1: 24 Henry Guesthouse is located just a few short blocks from
the heart of the Castro District. There are 5 beautifully furnished rooms in
this Victorian home, dating from the 1880's.
child:
pets:
wc:
id: 8647
historic: NULL
skiing: NULL
romance: NULL
antiques: NULL
family_fun: NULL
spas: NULL
luxury_: NULL
historic: NULL
*************************** 1. row ***************************
id: 1967
line: FULL
name: The Inn
city: San Francisco
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.thebandb.com
line: FULL
bookable:
cost_range_low: 80.00
cost_range_high: 420.00
showlink: 1
desc1:
child:
pets:
wc:
id: 1967
historic: NULL
skiing: NULL
romance: NULL
antiques: NULL
family_fun: NULL
spas: NULL
luxury_: NULL
historic: NULL
*************************** 2. row ***************************
id: 7954
line: FULL
name: The Hensley House
city: San Jose
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.hensleyhouse.com
line: FULL
bookable:
cost_range_low: 150.00
cost_range_high: 325.00
showlink: 1
desc1: 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.
child: ltd
pets: no
wc: no
id: 7954
historic: y
skiing:
romance: y
antiques: y
family_fun:
spas:
luxury_: y
historic: y
*************************** 3. row ***************************
id: 2193
line: FULL
name: Gerstle Park Inn
city: San Rafael
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.gerstleparkinn.com
line: FULL
bookable:
cost_range_low: 179.00
cost_range_high: 245.00
showlink: 1
desc1: All rooms have private baths, patio or decks. All are
furnished with rich fabrics and beautiful antiques. King suites with Jacuzzi
tub.
child: yes
pets: no
wc: ltd
id: 2193
historic: y
skiing:
romance: y
antiques: y
family_fun:
spas:
luxury_:
historic: y
*************************** 4. row ***************************
id: 12884
line: FULL
name: Gables Inn-Sausalito, The
city: Sausalito
country: USA
listcity: San Francisco
state: CA
liststate: CA
url: http://www.gablesinnsausalito.com
line: FULL
bookable:
cost_range_low: 135.00
cost_range_high: 325.00
showlink: 1
desc1: Originally built in 1869, the inn has recently been
completely renovated into a cozy B&B offering 9 distinctive suites artfully
decorated. Just 5 minutes north of the Golden Gate Bridge.
child: yes
pets: no
wc: yes
id: 12884
historic: y
skiing:
romance: y
antiques:
family_fun: y
spas:
luxury_: y
historic: y
Is this all you need? Thanks.
"Victoria Reznichenko" <[EMAIL PROTECTED]> wrote in message
news:aidphj$gj9$[EMAIL PROTECTED]...
> 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.his
toric,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.his
toric,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=bb&q=san+francisco&start=15&nu
mr=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
>
---------------------------------------------------------------------
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
