subquery a city

Hi all,

I've to link a person's address to the city table to know the state but
unfortunately the same ZIP may be in 2 different states (don't ask me
why....)

So I may have this in ZIP table:
ZIP CITYNAME STATE
1410 Thierrens VD
1410 Correvon VD
1410 Prevendavaux FR

So linking the city like this:
select STATE from person
left join city on person.ZIP = city.ZIP returns 2 rows.

I've tried to add a fulltext index in the city table and doing that

select STATE from person
left join city on (person.ZIP = city.ZIP and MATCH(city.city)
AGAINST(person.cityname))
isn't allowed as AGAINST need a string and can't work with a field name.

How can I fix this ?

Thanks for answering.

Bob

On 24 Oct, 08:06, "Bob Bedford" <b...@bedford.com> wrote:
> Hi all,
>
> I've to link a person's address to the city table to know the state but
> unfortunately the same ZIP may be in 2 different states (don't ask me
> why....)
>
> So I may have this in ZIP table:
> ZIP CITYNAME STATE
> 1410 Thierrens VD
> 1410 Correvon VD
> 1410 Prevendavaux FR
>
> So linking the city like this:
> select STATE from person
> left join city on person.ZIP = city.ZIP returns 2 rows.
>
> I've tried to add a fulltext index in the city table and doing that
>
> select STATE from person
> left join city on (person.ZIP = city.ZIP and MATCH(city.city)
> AGAINST(person.cityname))
> isn't allowed as AGAINST need a string and can't work with a field name.
>
> How can I fix this ?
>
> Thanks for answering.
>
> Bob

Where the zip is in 2 different states, how do you decide which one
you want to choose?

"Captain Paralytic" <paul_lautman@yahoo.com> a écrit dans le message de
news: 1193214095.056186.152550@y27g2000pre.googlegroups. com...
> On 24 Oct, 08:06, "Bob Bedford" <b...@bedford.com> wrote:
>> Hi all,
>>
>> I've to link a person's address to the city table to know the state but
>> unfortunately the same ZIP may be in 2 different states (don't ask me
>> why....)
>>
>> So I may have this in ZIP table:
>> ZIP CITYNAME STATE
>> 1410 Thierrens VD
>> 1410 Correvon VD
>> 1410 Prevendavaux FR
>>
>> So linking the city like this:
>> select STATE from person
>> left join city on person.ZIP = city.ZIP returns 2 rows.
>>
>> I've tried to add a fulltext index in the city table and doing that
>>
>> select STATE from person
>> left join city on (person.ZIP = city.ZIP and MATCH(city.city)
>> AGAINST(person.cityname))
>> isn't allowed as AGAINST need a string and can't work with a field name.
>>
>> How can I fix this ?
>>
>> Thanks for answering.
>>
>> Bob
>
> Where the zip is in 2 different states, how do you decide which one
> you want to choose?
I've tried a match against but it doesn't accept a field in an other table
(it only allow a string parameter). So the best would be to return the first
one in the list as this value isn't so important. I'll later create a
function for letting choose the best value, but for now I need a value,
whatever the value is.
Or better, if I can decide like
coalesce((select state where zip = 1410 and Cityname = 'Correvon'),(select
state where zip = 1410))

In the first case returns the correct state (the one that has the correct 2
values (zip and cityname), and returns the first occurence if it doesn't
find the correct match for the first check.

It is possible, how ?

Bob Bedford wrote:
> "Captain Paralytic" <paul_lautman@yahoo.com> a écrit dans le message
> de news: 1193214095.056186.152550@y27g2000pre.googlegroups. com...
>> On 24 Oct, 08:06, "Bob Bedford" <b...@bedford.com> wrote:
>>> Hi all,
>>>
>>> I've to link a person's address to the city table to know the state
>>> but unfortunately the same ZIP may be in 2 different states (don't
>>> ask me why....)
>>>
>>> So I may have this in ZIP table:
>>> ZIP CITYNAME STATE
>>> 1410 Thierrens VD
>>> 1410 Correvon VD
>>> 1410 Prevendavaux FR
>>>
>>> So linking the city like this:
>>> select STATE from person
>>> left join city on person.ZIP = city.ZIP returns 2 rows.
>>>
>>> I've tried to add a fulltext index in the city table and doing that
>>>
>>> select STATE from person
>>> left join city on (person.ZIP = city.ZIP and MATCH(city.city)
>>> AGAINST(person.cityname))
>>> isn't allowed as AGAINST need a string and can't work with a field
>>> name. How can I fix this ?
>>>
>>> Thanks for answering.
>>>
>>> Bob
>>
>> Where the zip is in 2 different states, how do you decide which one
>> you want to choose?
> I've tried a match against but it doesn't accept a field in an other
> table (it only allow a string parameter). So the best would be to
> return the first one in the list as this value isn't so important.
> I'll later create a function for letting choose the best value, but
> for now I need a value, whatever the value is.
> Or better, if I can decide like
> coalesce((select state where zip = 1410 and Cityname =
> 'Correvon'),(select state where zip = 1410))
>
> In the first case returns the correct state (the one that has the
> correct 2 values (zip and cityname), and returns the first occurence
> if it doesn't find the correct match for the first check.
>
> It is possible, how ?

I realise that English is not your first language. I'm afraid I really
cannot make any sense from what you have written above.

To make this easier, please supply a reasonable amout of sample data and the
expected results of the required query.