oude_query.sql

SELECT p.prijsID
FROM bbroch_bps_prijs AS p
INNER JOIN bbroch_bps_prijsoption AS table_formaat ON table_formaat.prijsID = p.prijsID AND table_formaat.attribName = 'formaat'
INNER JOIN bbroch_bps_prijsoption AS table_afwerking ON table_afwerking.prijsID = p.prijsID AND table_afwerking.attribName = 'afwerking'
INNER JOIN bbroch_bps_prijsoption AS table_papiersoort ON table_papiersoort.prijsID = p.prijsID AND table_papiersoort.attribName = 'papiersoort'
INNER JOIN bbroch_bps_prijsoption AS table_omvang ON table_omvang.prijsID = p.prijsID AND table_omvang.attribName = 'omvang'
INNER JOIN bbroch_bps_prijsoption AS table_multikleur ON table_multikleur.prijsID = p.prijsID AND table_multikleur.attribName = 'multikleur'
WHERE p.productID = 2 AND p.state = 1 AND (p.prijsEind > NOW() OR p.prijsEind = '0000-00-00')
AND p.prijsStart <= NOW() 

query.sql

SELECT p.prijsID
FROM bbroch_bps_prijs AS p
LEFT JOIN bbroch_bps_prijsoption AS po ON po.prijsID = p.prijsID 
     AND (po.attribName = 'formaat' AND po.prijsValue = 'A5_liggend' )
     AND (po.attribName = 'afwerking' AND po.prijsValue = 'Genaaid_gebonden_rechte_rug')
     AND (po.attribName = 'papiersoort' AND po.prijsValue = '115_grams_silk_mc')
     AND (po.attribName = 'omvang' AND po.prijsValue = '120')
     AND (po.attribName = 'multikleur' AND po.prijsValue = 'f044')
WHERE p.productID = 2 AND p.state = 1 AND (p.prijsEind > NOW() OR p.prijsEind = '0000-00-00') 
AND p.prijsStart <= NOW()
ORDER BY `p`.`prijsID` ASC

structure.sql

CREATE TABLE IF NOT EXISTS `bbroch_bps_prijs` (
  `prijsID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `prijsHash` varchar(255) NOT NULL,
  `drukkerijID` int(11) unsigned NOT NULL,
  `productID` int(11) unsigned NOT NULL,
  `machineType` varchar(64) NOT NULL,
  `prijsStart` date DEFAULT NULL,
  `prijsEind` date DEFAULT NULL,
  `prijsInkoop` decimal(15,2) NOT NULL DEFAULT '0.00',
  `prijsValue` decimal(15,2) NOT NULL,
  `prijsData` longtext NOT NULL,
  `state` tinyint(3) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `created_by` int(11) NOT NULL DEFAULT '0',
  `modified` datetime DEFAULT NULL,
  `modified_by` int(11) NOT NULL DEFAULT '0',
  `checked_out` int(11) NOT NULL DEFAULT '0',
  `checked_out_time` datetime DEFAULT NULL,
  `params` text,
  PRIMARY KEY (`prijsID`),
  KEY `prijsHash` (`prijsHash`),
  KEY `productID` (`productID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=413039 ;

CREATE TABLE IF NOT EXISTS `bbroch_bps_prijsoption` (
  `prijsoptionID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `prijsID` int(11) unsigned NOT NULL,
  `optionID` int(11) NOT NULL,
  `attribName` varchar(255) NOT NULL,
  `prijsValue` text NOT NULL,
  `prijsoptionData` longtext NOT NULL,
  PRIMARY KEY (`prijsoptionID`),
  KEY `prijsID` (`prijsID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3067405 ;