I have a question on how to substitute a subselect in mysql. For example lets say i have 3 tables Cars, Options and CarOptions
Cars consists of: uid, make Options consists of: uid, option CarOption consists of: uid, Caruid, Optionsuid I want to select all Cars that have ALL of requested options: if there were only few options (say 2) it would be easy: (lets ignore the options table and assume we know the needed uids from Options) SELECT Cars.* FROM Cars JOIN CarOptions as CO1 ON Cars.uid = CO1.Caruid JOIN CarOptions as CO2 ON Cars.uid = CO2.Caruid WHERE CO1.Optionuid = 1 AND CO2.Optionuid = 2 Now the problem is what if there are dozens of Options (so each car can have none or 20 options each).... You see the problem? I can easily keep on building JOIN statements for each option they requested but that could end up with a JOIN per Option that is requested.... Having no limit on Options the SELECT statement can be HUGE.... and adding additional tables into equation gets crazy (like tables carengines, carweel, cardealer)... So the queston is: Is there a better way of doing this? I am sure there is a limit on how many joins can be in the SELECT... Also, I tested it with the following data: 3 entries in the Cars table, 20 entries in Option table and 10 entries in the CarOption table.... Wrote the SELECT asking for Every possible option (all 20) and the time it took to execute it was devistating.... (about 2 seconds) (longer than if i manualy did select from CarOption table for every needed Option for each car). Please help, thank you
