Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes.
Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP TABLE IF EXISTS `Sizes`; CREATE TABLE `Sizes` ( `idsizes` int(10) unsigned NOT NULL auto_increment, `size` char(4) default NULL, `jpSize` char(4) default NULL, PRIMARY KEY (`idsizes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; On 2/9/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: > > Hi Conor, > > The table you showed us has 2 primary keys, which is not possible. > Can you do a SHOW CREATE TABLE on *each* table? > > -Sheeri > > On 2/8/06, Conor McTernan <[EMAIL PROTECTED]> wrote: > > Hello, I'm having a hell of a time figuring this query out, maybe > someone > > can point me in the right direction. > > > > I have a table which lists the case configurations for cases of items. > Each > > case will have a different combination of sizes e.g. Case 002 has 2 size > 5, > > 2 size 7 and 2 size 8 items. > > > > What I'm trying to do is, given a group of sizes and quantities how can > I > > find the corresponding ID number for a case. > > > > Here's the description of the table. > > > +---------------------+------------------+------+-----+---------+-------+ > > | Field | Type | Null | Key | Default | Extra > | > > > +---------------------+------------------+------+-----+---------+-------+ > > | CaseType_idCaseType | int(10) unsigned | NO | PRI | | > | > > | Sizes_idsizes | int(10) unsigned | NO | PRI | | > | > > | qty | int(10) unsigned | YES | | NULL | > | > > > +---------------------+------------------+------+-----+---------+-------+ > > > > > > Here's what I see if I do a select on a CaseType id. > > > > select * from CaseType_has_Sizes where CaseType_idCaseType = 61; > > > > +---------------------+---------------+------+ > > | CaseType_idCaseType | Sizes_idsizes | qty | > > +---------------------+---------------+------+ > > | 61 | 2 | 1 | > > | 61 | 4 | 1 | > > | 61 | 6 | 1 | > > | 61 | 8 | 1 | > > | 61 | 24 | 1 | > > | 61 | 26 | 1 | > > +---------------------+---------------+------+ > > > > I'm essentially trying to get the same resuts as this select, but in > > reverse.... > > > > I've tried using UNION but it doesnt seem to get what I'm trying for. > > > > Any help appreciated. > > > > Conor > > > > >
