4 Replies Latest reply on Jul 27, 2007 11:43 AM by Newsgroup_User

    ASP SQL Access distinct from one column

    Level 7
      I have a query from two tables: table company and tables subcategories
      A company can have many subcategories.
      In the query I have put the fields id_company, company, keywords from
      table company and subcategories from table categories.
      When I use the select distinct * (or field names) the sql returns all
      the data whit double company's. This is normal, there different by
      subcategories. I need the subcategories to use for a search field. I
      can delete the categories field from my query and put the names
      manually in the keywords field.
      Is there a way to use the distinct only on the id_comany field so the
      sql returns only one company whit many subcategories?
      I hope this makes sense, can somebody helping my out?
      Thanks in advance,
      Sam


        • 1. Re: ASP SQL Access distinct from one column
          bregent Most Valuable Participant
          It doesn't really make sense. If a company can have many subcategories but you only want to see a company listed once, then why are you outputting the subcategory? If you want to list a company once, which subcategory would you choose to be listed with it? If you're not displaying all subcategories, why display any?
          • 2. Re: ASP SQL Access distinct from one column
            SamDesign Level 1
            Hello Bregent,

            I know, but I need the subcategories to search inside the
            subcategories column.
            I made a text field to search inside deferent columns, keywords,
            description, ... and subcategories. So when people search for "cars" the
            sql will search in the subcategories for the companys whit the
            subcategorie "cars". The company can have more than one subcategorie,
            like cars, motorcycle and trucks. The sql will return the 3 companys
            in my result page, but I won't only one listed (the 3 companys are the
            same)
            When there is no solution for it, I can ad the different subcategories
            in the keywords field so I don't need the subcategories anymore in my
            sql.
            Thanks in advance for your help!
            Sam
            • 3. Re: ASP SQL Access distinct from one column
              bregent Most Valuable Participant
              Sorry Sam, I am not completely understanding the problem. Can you please provide more details about your database schema and include sample data?
              • 4. Re: ASP SQL Access distinct from one column
                Level 7
                Table = companys - fields = id_company, company, keywords

                Table = subcategories - fields = id_subcategorie, subcategorie and
                id_company with a relation to the table companys.

                The companys can have many subcategories.

                I created a query "qry_search" with the fields id_company, company,
                keywords, subcategorie

                in the page with the text field "search" I created a
                Request.QuerySting.varcompany, Request.QuerySting.varkeywords and
                Request.QuerySting.varsubcategorie

                In the result page I have a recordset whit:



                SELECT id_company, company, keywords, subcategorie

                FROM qry_search

                WHERE company LIKE %varcompany% OR keywords LIKE %varkeywords% OR
                subcategorie LIKE %varsubcategorie%



                I leave the DISTINCT function for this moment away



                Example DB qry_search:

                ID company keywords
                subcategorie

                1 BMW bmw new sales luxe cars

                1 BMW bmw new sales luxe garage

                2 Sam sandwich lunch wine restaurant

                2 Sam sandwich lunch wine snack

                3 Audi Audi new sales luxe cars



                When you put in the text field "search" bmw, I have 2 results for the same
                company

                When you put in the text field "search" cars, I have 2 results but different
                companys

                When you put in the text field "search" sales, I have 3 results but 2 for
                the same company and 1 for the Audi company



                I hope you can help me otherwise I will ad the subcategories in the keyword
                field and don't search in the subcategorie field.

                Thanks for your time Bregent !!!



                "bregent" <webforumsuser@macromedia.com> a écrit dans le message de news:
                f8d7vn$4pg$1@forums.macromedia.com...
                > Sorry Sam, I am not completely understanding the problem. Can you please
                > provide more details about your database schema and include sample data?