SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Duplicate Results

    I am expecting for only 5 rows based on my query, however, it returns 25 rows. How is this happen? Can anyone help me with my statement. Below is my SQL statement

    Code:
    SELECT subcriteria.id, subcriteria.criteria, subcriteria.percentage, tally.score FROM tally
    INNER JOIN subcriteria ON subcriteria.awardid=tally.categoryid WHERE tally.contestantid=1
    AND tally.categoryid=1 AND tally.judgeid='1' AND tally.competitionid='00001'

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2010
    Location
    Canada, Alberta
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you saying the it returns the rows 25 times with the same data or do you want to limit the returned rows to 5?

  3. #3
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There were only 5 records that were stored in my tally table, based on the query given, it returns 25 records, which means that a particular record is repeatedly displayed 5 times.

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2010
    Location
    Canada, Alberta
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't say for sure but if you want to match records that are only on the tally table than you might want to try using LEFT JOIN instead of INNER JOIN. You can also try using distinct on you mysql statement. ex
    Code:
    SELECT DISTINCT 'row-name' FROM etc...
    distinct can weed out duplicate values but only if you have duplicate values in the selected table, ex. if there are 3 people with last names that are equal to 'smith'.

  5. #5
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I already include DISTINCT keyword but the same results was displayed.

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2010
    Location
    Canada, Alberta
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and left join didn't work?

  7. #7
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, even right join

  8. #8
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Attach is an image that shows the result of my query.
    Attached Images Attached Images

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2010
    Location
    Canada, Alberta
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I noticed that the tally.score repeats after every 5 rows. It's a bit difficult to tell since I don't have the database to test with, it'd help if you could post the .sql file of the database.

  10. #10
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I back-up the content my database using MySQL Administrator.
    Attached Files Attached Files

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2010
    Location
    Canada, Alberta
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, from what I could see, there's no way to tell which score is associated with criteria. it's probably best if you add a relationship between the tally.categoryid to the subcriteria.id that way we can associate a score with a subcriteria

    try this
    Code:
    CREATE DATABASE IF NOT EXISTS tabulator;
    USE tabulator;
    
    --
    -- Definition of table `subcriteria`
    --
    
    DROP TABLE IF EXISTS `subcriteria`;
    CREATE TABLE `subcriteria` (
      `id` double NOT NULL AUTO_INCREMENT,
      `criteria` varchar(255) NOT NULL,
      `percentage` tinyint(3) unsigned NOT NULL,
      `awardid` double NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `subcriteria`
    --
    
    /*!40000 ALTER TABLE `subcriteria` DISABLE KEYS */;
    INSERT INTO `subcriteria` (`criteria`,`percentage`,`awardid`) VALUES
     ('Poise and Grace',40,1),
     ('Figure and Beauty',20,1),
     ('Projection and Stage Presence',20,1),
     ('First Impression',10,1),
     ('Totality of Elements',10,1);
    /*!40000 ALTER TABLE `subcriteria` ENABLE KEYS */;
    
    
    --
    -- Definition of table `tally`
    --
    
    DROP TABLE IF EXISTS `tally`;
    CREATE TABLE `tally` (
      `id` double NOT NULL AUTO_INCREMENT,
      `score` tinyint(3) unsigned NOT NULL,
      `contestantid` double NOT NULL,
      `judgeid` double NOT NULL,
      `criteriaid` double NOT NULL,
      `competitionid` varchar(5) NOT NULL,
      `categoryid` double NOT NULL,
      PRIMARY KEY (`id`)
    ,FOREIGN KEY(categoryid)REFERENCES subcriteria(id) ON UPDATE CASCADE ON DELETE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `tally`
    --
    
    /*!40000 ALTER TABLE `tally` DISABLE KEYS */;
    INSERT INTO `tally` (`id`,`score`,`contestantid`,`judgeid`,`criteriaid`,`competitionid`,`categoryid`) VALUES
     (43,88,1,1,1,'00001',(select id from subcriteria where criteria = 'Poise and Grace')),
     (44,89,1,1,2,'00001',(select id from subcriteria where criteria = 'Figure and Beauty')),
     (45,90,1,1,3,'00001',(select id from subcriteria where criteria = 'Projection and Stage Presence')),
     (46,91,1,1,4,'00001',(select id from subcriteria where criteria = 'First Impression')),
     (47,92,1,1,5,'00001',(select id from subcriteria where criteria = 'Totality of Elements'));
    /*!40000 ALTER TABLE `tally` ENABLE KEYS */;
    and change the sql statement to

    Code:
    SELECT subcriteria.id, subcriteria.criteria, subcriteria.percentage, tally.score FROM tally
    INNER JOIN subcriteria ON subcriteria.id=tally.categoryid WHERE tally.contestantid=1
    AND tally.judgeid='1' AND tally.competitionid='00001'

  12. #12
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •