Duplicate Results

[sirGE]

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

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'

[charlestonolaes]

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?

[sirGE]

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.

[charlestonolaes]

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

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'.

[sirGE]

I already include DISTINCT keyword but the same results was displayed.

[charlestonolaes]

and left join didn't work?

[sirGE]

Attach is an image that shows the result of my query.

[sirGE]

yeah, even right join

[charlestonolaes]

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.

[sirGE]

I back-up the content my database using MySQL Administrator.

[charlestonolaes]

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

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

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'

[sirGE]

Thanks a lot.