Help with SQL Query Required

[stuart]

Hello,

I have a table in database that contains property information. I need to
select the distinct village that each property but the Village name is
in a column with the Property name and seperated by a comma.

Example Data::

RecordID PropertyName Town
1 MyHouse 1, big Village Main Town
2 MyHouse 2, big Village Main Town
3 MyHouse 3, big Village Main Town
4 MyHouse 1, Small Village Main Town
5 MyHouse 2, Small Village Main Town
6 MyHouse 3, Small Village Main TOwn
7 MyHouse 1, Tiny Village Main TOwn
8 MyHouse 2, Tiny Village Main Town
9 MyHouse 3, Tiny Village Main Town
10 MyHouse 4, Tiny Village Main TOwn

I want the DB to return the following result: -

Big Village
Small Village
Tiny Village

This is the data I have to work with and there is no way of changing the
schema of the DB as I have no control over it.

Any ideas how I would do this?

Regards,

Stuart

 

[Stuart Palmer]

To do this you could get distinct name from the DB in order, and then split
each into an array, if previous one is the same as current ignore it.

I don't think there is a way you can do this in a SQL query, just a bunch of
jigger pokery to get the output you want.

Obviously, if you could add a new field to the db it would be far easier,
but thats easy for me to say.

Good luck.

Stu

 

[Bob Barrows [MVP]]

Hello,

I have a table in database that contains property information. I need
to select the distinct village that each property but the Village
name is in a column with the Property name and seperated by a comma.

Example Data::

RecordID PropertyName Town
1 MyHouse 1, big Village Main Town
2 MyHouse 2, big Village Main Town
3 MyHouse 3, big Village Main Town
4 MyHouse 1, Small Village Main Town
5 MyHouse 2, Small Village Main Town
6 MyHouse 3, Small Village Main TOwn
7 MyHouse 1, Tiny Village Main TOwn
8 MyHouse 2, Tiny Village Main Town
9 MyHouse 3, Tiny Village Main Town
10 MyHouse 4, Tiny Village Main TOwn

I want the DB to return the following result: -

Big Village
Small Village
Tiny Village

Does every record have "Main Town" in it? Does that have to be removed as
well? Or is "Main Town" in a separate field ... ? I guess it looks like
we're dealing with 3 fields, so I will go on that assumption.

What type and version of database are you using?

Bob Barrows

 

[stuart]

Bob,

I am using Foxpro (not through choice I may add). The column containing
'main town' could be another town name.

I know that this is not the best DB design in the world but it has been
forced on my as another system is using the DB for the interface and
update of data. I am only using it for the display of data.

 

[Bob Barrows [MVP]]

I cannot help with foxpro syntax. Please try a foxpro group.

Bob Barrows