Help with SQL Query Required

Discussion in 'ASP General' started by stuart, Jan 4, 2005.

  1. stuart

    stuart Guest

    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, Jan 4, 2005
    #1
    1. Advertising

  2. 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
    Stuart Palmer, Jan 4, 2005
    #2
    1. Advertising

  3. 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.
    Bob Barrows [MVP], Jan 4, 2005
    #3
  4. stuart

    stuart Guest

    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
    >
    stuart, Jan 4, 2005
    #4
  5. 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.
    Bob Barrows [MVP], Jan 4, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Just D.
    Replies:
    0
    Views:
    446
    Just D.
    Mar 10, 2006
  2. Edward
    Replies:
    4
    Views:
    4,595
    William \(Bill\) Vaughn
    Apr 10, 2006
  3. Anonymous
    Replies:
    0
    Views:
    1,455
    Anonymous
    Oct 13, 2005
  4. ecoolone
    Replies:
    0
    Views:
    752
    ecoolone
    Jan 3, 2008
  5. Vp
    Replies:
    1
    Views:
    274
    Cowboy \(Gregory A. Beamer\)
    Jul 21, 2008
Loading...

Share This Page