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]]

stuart wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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] wrote:

stuart wrote:

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

[Bob Barrows [MVP]]

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

Bob Barrows
stuart wrote:

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] wrote:

stuart wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.