Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP General (http://www.velocityreviews.com/forums/f65-asp-general.html)
-   -   Help with SQL Query Required (http://www.velocityreviews.com/forums/t797509-help-with-sql-query-required.html)

stuart 01-04-2005 02:18 PM

Help with SQL Query Required
 
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 01-04-2005 02:32 PM

Re: Help with SQL Query Required
 
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] 01-04-2005 02:45 PM

Re: Help with SQL Query Required
 
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 01-04-2005 02:50 PM

Re: Help with SQL Query Required
 
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] 01-04-2005 02:53 PM

Re: Help with SQL Query Required
 
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.




All times are GMT. The time now is 05:09 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.