This is what you need. Take a look and if you still have questions, let me know. http://www.oracle-developer.net/display.php?id=303
On Sun, Jan 8, 2012 at 3:10 AM, Sathya Narayanan <cred...@gmail.com> wrote: > In our application the following data model constitutes to one of the most > expensive queries in the database. > The question is this: find the item_groups that has a given property in a > given store > > A given item group belongs to a store if both of the following conditions > are satisfied > 1. All the items in the item group should belong to the store in > ITEM_LIST_IN_STORE. > 2. All the items in the item group should have a translation in the > ITEM_NAME for the country/language to which the store belongs > > I have tried several ways to answer this question and have always ended up > thinking there should be a better way to answer this. > I have found it literally impossible to do this without generating a > cartesian product at some point in the query. > I am placing this problem in this forum in the hope that some of you may > be able to figure out a better solution than me. > > CREATE TABLE ITEM_LIST_IN_STORE > ( > ITEM_NO VARCHAR2(15), > STORE_NO VARCHAR2(5), > DELETE_DATE DATE > ) > / > > CREATE TABLE ITEM_GROUPS > ( > ITEM_GROUP_NO VARCHAR2(15), > ITEM_NO_CHILD VARCHAR2(15) > ) > / > > CREATE TABLE ITEM_NAME > ( > ITEM_NO VARCHAR2(15), > ITEM_NAME VARCHAR2(55), > CTY_CODE VARCHAR2(5), > LANG_CODE VARCHAR2(5), > DELETE_DATE DATE > ) > / > > CREATE TABLE ITEM_PROPS > ( > ITEM_NO VARCHAR2(15), > ITEM_NAME_OFFICIAL VARHCAR2(55), > PRODUCT_NAME_NO VARHCAR2(55), > PRODUCT_TYPE_NO VARCHAR2(55) > ) > / > > CREATE TABLE STORE_CTY_LANG > ( > STORE_NO VARCHAR2(5), > CTY_CODE VARCHAR2(5), > LANG_CODE VARCHAR2(5) > DELETE_DATE DATE > ) > / > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
