Sheeri, Thanks for the help. I tried your sample queries, but they dont really return what I'm looking for. I think I've found a solution though.
Given the contents of a case, I'm looking for a unique case id, basicially I want to search for a case if it exists once I've decided the configuration I want. What I'm doing now is I alias the table I'm searching on for each Size, Qty combination, and create a self join on this and the Case_Type_idCaseType id. Here's an example of what I'm doing, I know CaseType_idCaseType '1' exists, and that it's make up is: +---------------------+---------------+------+ | CaseType_idCaseType | Sizes_idsizes | qty | +---------------------+---------------+------+ | 1 | 10 | 1 | | 1 | 11 | 1 | | 1 | 12 | 1 | | 1 | 13 | 1 | | 1 | 14 | 1 | | 1 | 15 | 1 | +---------------------+---------------+------+ Taking these Sizes_idSizes and qty combinations I want to see if I can find the CaseType_idCaseType by itself. SELECT c10.CaseType_idCaseType AS case_id FROM CaseType_has_Sizes AS c10, CaseType_has_Sizes AS c11, CaseType_has_Sizes AS c12, CaseType_has_Sizes AS c13, CaseType_has_Sizes AS c14, CaseType_has_Sizes AS c15 WHERE c10.Sizes_idsizes = 10 AND c10.qty = 1 AND c11.Sizes_idsizes = 11 AND c11.qty = 1 AND c12.Sizes_idsizes = 12 AND c12.qty = 1 AND c13.Sizes_idsizes = 13 AND c13.qty = 1 AND c14.Sizes_idsizes = 14 AND c14.qty = 1 AND c15.Sizes_idsizes = 15 AND c15.qty = 1 AND c10.CaseType_idCaseType = c11.CaseType_idCaseType AND c11.CaseType_idCaseType = c12.CaseType_idCaseType AND c12.CaseType_idCaseType = c13.CaseType_idCaseType AND c13.CaseType_idCaseType = c14.CaseType_idCaseType AND c14.CaseType_idCaseType = c15.CaseType_idCaseType; It's a hell of a query, and I'm sure there's a better way to do it, but it will give me what I'm looking for. +---------+ | case_id | +---------+ | 1 | | 19 | | 37 | | 42 | | 44 | | 76 | | 110 | | 157 | | 552 | +---------+ It's returned 9 cases on this query, but from testing it out, the first case_id returned is the one I'm looking for, all the other cases are ones that are larger than the one i searched on that include the size, qty combinations I specified in the query. If I add a 'LIMIT 1' at the end it will return only the case_id I'm looking for. As I said, I'm sure there's a better way to do this, and if anyone has any suggestions I'd only be happy to listen. Conor On 2/10/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: > > 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 > > > > > > > > > > > > > > > >
