locked
Why it don't fetch DISTINCT category from database RRS feed

  • Question

  • User2072242043 posted

    Hi everyone,

    I have a table with id and Category columns. i want to fetch only the DISTINCT Category from database but it couldn't do as expected.it gives me output as all categories from table.

    I have this block of code:

     var selectUniqueCategory="SELECT DISTINCT(Category),id FROM images";
                              var SelectedUniqueCat=db.Query(selectUniqueCategory,category,id);
    
    
     @foreach(var row in SelectedUniqueCat){
    
                      
              <p><a href="#" data-target="#row_thumbnails">@row.Category </a>
                  <a href="/EditCategory?id=@row.id">&nbsp;<span class="glyphicon glyphicon-pencil"></span></a></p>

    In for each loop i want to perform two tasks:

    1: Get the DISTINCT Category from table

    2: Fetch id column and pass each id related with that category to id=@row.id

    If i remove id from Query, like:

    var selectUniqueCategory="SELECT DISTINCT(Category) FROM images";
                              var SelectedUniqueCat=db.Query(selectUniqueCategory,category);

    it works fine to get the DISTINCT Category from table but i want to fetch both Category as DISTINCT and id as primary in table.

    I hope i will hear as soon as possible from Mike

    Thanks in Advance

    Tuesday, July 14, 2015 2:19 AM

Answers

  • User2072242043 posted

    Hi,

    You really taught me about DISTINCT. I analyze my whole code after your comments and found the solution what i was intending to get.

    Here is the code part of my solution:

     <div style="margin-left: 25px;
                          font-size: 15px;">
    
      
                  @foreach(var row in SelectedUniqueCat){
                    
                        
                    
                      
              <p><a href="#row_thumbnails">@row.Category </a>
                   <a href="/EditCategory?id=@{foreach( var SendId in selectedData )
                                               {
                                                   if(row.Category==SendId.Category)
                                                   {
                                                       @SendId.id
                                                   }
                                               }}" >&nbsp<span class="glyphicon glyphicon-pencil"></span></a></p>
                  }
           
                   
    
    </div>

    As earlier i said i wan to fetch Distinct category and id associated with it. Now i am done with that.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 15, 2015 12:43 AM

All replies

  • User1853794821 posted

    What you require is illogical.

    Assuming that each ID, itself, the unique Id of an image, then SELECT DISTINCT id, category will always return a set of unique combinations of (image) id and category. 

    This appears to be a normalization issue.  I cannot tell from your question what your data structure is.  Most cases like this would involve two tables: (1) a Category table with (category)ID and Category, and (2) an Image table, with (image) id, Image data, and a categoryId (foreign key) which references the id in the category table.

    Tuesday, July 14, 2015 8:16 AM
  • User379720387 posted

    SELECT DISTINCT id, Category FROM images ORDER BY Category

    Tuesday, July 14, 2015 8:41 AM
  • User2072242043 posted

    In my case,What i want is confidentially logical. here is my complete code so that you can analyze what i want to do.

    @{
        Layout="_nav.cshtml";
     
        var db=Database.Open("Task5");
     
        WebImage photo = null;
        var newFileName = "";
        var imagePath = "";
       
        
        var category=Request.Form["category"];
        var id=Request.Form["id"];
                     
     
        if(IsPost){
            
            photo = WebImage.GetImageFromRequest();
            if(photo != null){
                newFileName = Guid.NewGuid().ToString() + "_" +
                    Path.GetFileName(photo.FileName);
                imagePath = @"img\images\" + newFileName;
     
                photo.Save(@"~\" + imagePath);
                
            }
                        
                         
                                                         
                             var queryString="INSERT into images(Category,Image) VALUES (@0,@1) ";
                             db.Execute(queryString,category,imagePath);  
     
                             <div class="alert alert-success">
                         <strong>Great! </strong> Submitted Successfully.
                             </div>
                                                        
            
    }  
                              var SelectQuery="SELECT * FROM images";
                              var selectedData=db.Query(SelectQuery,category,imagePath);
     
                              var selectUniqueCategory="SELECT DISTINCT(Category),id FROM images";
                              var SelectedUniqueCat=db.Query(selectUniqueCategory,category),id;
          
                                        
           }
             
                     
                     
     
      
    <div class="container">
        
      <div id="sidebar" class="sidebar-offcanvas ">
          <div class="col-md-12">
            <h4 style="text-align: center;
                       font-family: cursive;
                       padding-top: 12px;
                       font-size: large;
                       color: darkseagreen;
                ">Select Image</h4>
                 <form action="" method="post" enctype="multipart/form-data">
        <hr>
                     <div style="margin-left: 10px;">
        <div class="form-group">
            <label for="category" style="
                       font-family: cursive;
                       padding-top: 12px;
                       font-size: 22px;
                       color: cadetblue; ">Enter Category</label>
           
            <p><input type="text" class="form-control" name="category"></p>
            
            
        </div>
            <input type="file" name="Image" />
            <br/>
            <input type="submit" value="Upload" />
       </div>
    <hr>
      </form>
                  
            </div>
          
              <div style="margin-left: 25px;
                          font-size: 15px;">
     @foreach(var row in SelectedUniqueCat){
                    
                           
              <p><a href="#" data-target="#row_thumbnails">@row.Category </a>
                   <a href="/EditCategory?id=@row.id" >&nbsp;<span class="glyphicon glyphicon-pencil"></span></a></p>
                  }
    </div> </div> </div>

    Here at this point of code:

    @foreach(var row in SelectedUniqueCat){
                    
                        
                    
                      
              <p><a href="#" data-target="#row_thumbnails">@row.Category </a>
                   <a href="/EditCategory?id=@row.id" >&nbsp;<span class="glyphicon glyphicon-pencil"></span></a></p>
                  }

    I want to fetch DISTINCT categories and id related with that category from images table.

    Reason to fetch id and Category Simultaneously:

    I want to show Distinct categories and beside these categories i have edit link using Edit glyph icon(bootstrap). when user click that link it will should redirect to EditCategory page with the id associated with that category to edit that category if user wants.

    When i try with this whole code i face No any error, i works fine with duplicate categories and id's associated with them.

    The reason of this whole story is that i only don't get DISTINCT Categories, that's all.

     

    Tuesday, July 14, 2015 9:13 AM
  • User1853794821 posted

    Do you have a separate categories table?  Please show your data structure. 

    I do not know what id you are referring to.  However, if it is the id of the image, then your "SELECT DISTINCT ID, CATEGORY" will not return distinct categories.  That is what I meant as illogical.

    Tuesday, July 14, 2015 9:30 AM
  • User1853794821 posted

    What is your data structure?

    You do not appear to have a separate Categoru table.  It appears from your code that the "id" in "SELECT DISTINCT ID, Category FROM IMAGES" is, thus, the id of the image.  Such a query can never provide distinct categories.  It can only provide distinct combinations of image id and category.

    There also appears to be an issue with your code:

      //this has no parameters
      var SelectQuery="SELECT * FROM images";
      //this provides values for non-existent parameters
      var selectedData=db.Query(SelectQuery,category,imagePath);
    
      //this has no parameters
      var selectUniqueCategory="SELECT DISTINCT(Category),id FROM images";
      //this provides values for non-existent parameters; 
      //it also appears to have a misplaced ending parenthesis
      var SelectedUniqueCat=db.Query(selectUniqueCategory,category),id;

    Tuesday, July 14, 2015 9:41 AM
  • User2072242043 posted

    I think there is no difference b/w 

    SELECT DISTINCT(Category),id FROM images

    and

    SELECT DISTINCT id, Category FROM images ORDER BY Category

    Tuesday, July 14, 2015 9:51 AM
  • User2072242043 posted

    No i don't have separate tables.

    I have only one table with 

    id as primary key
    Category as varchar
    image as varchar(Which holds path of the image only)

    Yes it is the id of the image(as path) and category(as varchar).

    Can you please tell me the reason why it will not return distinct categories?

    Tuesday, July 14, 2015 10:09 AM
  • User2072242043 posted

    I apologize, i have a mistake there

    var SelectedUniqueCat=db.Query(selectUniqueCategory,category),id;

    it should be

    var SelectedUniqueCat=db.Query(selectUniqueCategory,category,id);

    Can you please correct me how i should go to do so as i i want?
    Any suggestions?

    Tuesday, July 14, 2015 10:26 AM
  • User379720387 posted

    var SelectedUniqueCat = db.Query("SELECT DISTINCT id, Category FROM Images ORDER BY Category");

    There is no need for , category, id

    Tuesday, July 14, 2015 11:39 AM
  • User1853794821 posted

    The reason it will not return what you want is because it returns what you asked for.

    Suppose you have three images:

    • (1) ID=1,  category = 'sky', image = <image1 stuff>
    • (2) ID=2,  category = 'sky', image = <image2 stuff>
    • (3) ID=3,  category = 'land', image = <image3 stuff>

     SELECT DISTINCT id, Category FROM Image will give you each distinct COMBINATION of id AND category:

    • 1+'sky'
    • 2+'sky'
    • 3+'land'

    Note that the COMBINATION of 1 with 'sky' is distinct and different from the COMBINATION of 2 and 'sky'

    That is how it is designed.  See http://www.sql-tutorial.com/sql-distinct-sql-tutorial/

    From the code you originally provided, it looks like what you are trying to do is update the value of the category column in every image row which uses that category value. 

    If this is the case, then something like the following will accomplish that:

    var oldCategory='sky';
    var newCategory='the heavens';
    var updQry="UPDATE image SET Category=@0 WHERE category=@1";
    var numberOfRecordsUpdated=db.Execute(updqry,newCategory, oldcategory);


     

    Tuesday, July 14, 2015 12:00 PM
  • User2072242043 posted

    Hi,

    You really taught me about DISTINCT. I analyze my whole code after your comments and found the solution what i was intending to get.

    Here is the code part of my solution:

     <div style="margin-left: 25px;
                          font-size: 15px;">
    
      
                  @foreach(var row in SelectedUniqueCat){
                    
                        
                    
                      
              <p><a href="#row_thumbnails">@row.Category </a>
                   <a href="/EditCategory?id=@{foreach( var SendId in selectedData )
                                               {
                                                   if(row.Category==SendId.Category)
                                                   {
                                                       @SendId.id
                                                   }
                                               }}" >&nbsp<span class="glyphicon glyphicon-pencil"></span></a></p>
                  }
           
                   
    
    </div>

    As earlier i said i wan to fetch Distinct category and id associated with it. Now i am done with that.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 15, 2015 12:43 AM