You originally mention your UNION "doesn't work" but you did not specify the query. This is a simple or query, or union. You can do either:
select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4); or select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=2 UNION select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=4; Please let me know if it's more complex than that, but when I ran it on my test data I got +---------------------+---------------+------+ | CaseType_idCaseType | Sizes_idsizes | qty | +---------------------+---------------+------+ | 60 | 2 | 1 | | 60 | 4 | 1 | | 61 | 2 | 1 | | 61 | 4 | 1 | +---------------------+---------------+------+ for both results. -Sheeri On 2/8/06, Conor McTernan <[EMAIL PROTECTED]> wrote: > 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 > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
