How to use one query instead of subqueries with CONCAT() function?

Question

SELECT entID,entHelmName, ecnID  FROM `tblentries` 
JOIN `tblCategories` ON ecnEvent = entEvent
WHERE ecnEvent = 52 AND
CONCAT(entID,'#',ecnID) NOT IN (SELECT CONCAT(ecyEntry,'#',eciCategory)  FROM `tblentryCategories` JOIN tblcategoryitems ON eciID = ecyItem JOIN tblEntries ON ecyEntry = entID WHERE entEvent = 52);

entID   entHelmName ecnID
1661    Bernice Hulme   27
1661    Bernice Hulme   28
1654    Bill Kenyon     28
1655    David Morris    28

Context: There are Events, which sailors can enter to race (and are scored). Sailors can be assigned values in categories which are specific to each event.

Example: in Event 52 Categories are called Fleet=27 and Division=28 (Fleet values are Silver,Gold, Division values are Adult,Junior)

Bernice has no choice set for either Fleet or Division so 2 rows in results
Bill is in "Gold Fleet" but has no choice set for Division
David is in "Silver Fleet" but has no choice set for Division

Here are the table contents, table structures below. (Note foreign keys on tblentrycategories ecyEntry->entID and ecyItem->eciID, and tblCategories ecnID->eciCategory)

Table content (showing relevant rows only)

tblntries:

SELECT `entID`, `entEvent`, `entHelmName` 
FROM `tblentries` 
WHERE entEvent=52;

entID entEvent  entHelmName
1661    52  Bernice     
1654    52  Bill    
1655    52  David   
1653    52  Deborah 
1662    52  Michaela

tblcategories:

SELECT * 
FROM `tblcategories` 
WHERE `ecnEvent`=52;

ecnID ecnEvent  ecnName 
28  52  Division    
27  52  Fleet

tblcategoryitems:

SELECT * 
FROM `tblcategoryitems` 
WHERE `eciCategory` IN (27,28);

eciID eciCategory eciValue  
55  27  Gold    
54  27  Silver  
57  28  Adult   
56  28  Junior

tblentrycategories:

SELECT `tblentrycategories`.*, `entID`,`entEvent`,`entHelmName` 
FROM `tblentrycategories` 
JOIN `tblentries` 
WHERE `ecyEntry` = `entID` AND `entEvent` = 52;

ecyEntry ecyItem    entID   entEvent entHelmName    
1654    55      1654    52  Bill    
1655    54      1655    52  David   
1653    55      1653    52  Deborah 
1653    57      1653    52  Deborah
1662    54      1662    52  Michaela    
1662    56      1662    52  Michaela

Table structures:

CREATE TABLE `tblentries` (
  `entID` int(11) NOT NULL AUTO_INCREMENT,
  `entSeries` int(11) DEFAULT NULL,
  `entEvent` int(11) NOT NULL,
  `entHelmName` varchar(50) NOT NULL,
  `entClub` varchar(50) DEFAULT NULL,
  `entClass` int(11) NOT NULL,
  PRIMARY KEY (`entID`),
  UNIQUE KEY `ixentEventHelmClassFleet` (`entEvent`,`entSeries`,`entHelmName`,`entFleet`,`entClass`),
  CONSTRAINT `fkentEvent` FOREIGN KEY (`entEvent`) REFERENCES `tblevents` (`evtID`),
) ENGINE=InnoDB AUTO_INCREMENT=1589 DEFAULT CHARSET=utf8;

CREATE TABLE `tblcategories` (
  `ecnID` int(11) NOT NULL AUTO_INCREMENT,
  `ecnEvent` int(11) NOT NULL,
  `ecnName` varchar(50) NOT NULL,
  PRIMARY KEY (`ecnID`),
  UNIQUE KEY `ixecnEventName` (`ecnEvent`,`ecnName`),
  KEY `ixecnEvent` (`ecnEvent`),
  CONSTRAINT `fkecnEvent` FOREIGN KEY (`ecnEvent`) REFERENCES `tblevents` (`evtID`),
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

CREATE TABLE `tblcategoryitems` (
  `eciID` int(11) NOT NULL AUTO_INCREMENT,
  `eciCategory` int(11) NOT NULL,
  `eciValue` varchar(50) NOT NULL,
  PRIMARY KEY (`eciID`),
  UNIQUE KEY `ixeciCategoryValue` (`eciCategory`,`eciValue`),
  KEY `ixeciCategory` (`eciCategory`),
  CONSTRAINT `fkeciCategory` FOREIGN KEY (`eciCategory`) REFERENCES `tblcategories` (`ecnID`),
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8;

CREATE TABLE `tblentrycategories` (
  `ecyEntry` int(11) NOT NULL DEFAULT 0,
  `ecyItem` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ecyEntry`,`ecyItem`),
  KEY `ixecyEntry` (`ecyEntry`),
  KEY `ixecyItem` (`ecyItem`),
  CONSTRAINT `fkecyEntry` FOREIGN KEY (`ecyEntry`) REFERENCES `tblentries` (`entID`),
  CONSTRAINT `fkecyItem` FOREIGN KEY (`ecyItem`) REFERENCES `tblcategoryitems` (`eciID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

How to use one query instead of subqueries with CONCAT() function?

0 Answers0