11 Replies Latest reply: Jan 4, 2013 2:28 PM by Frank Kulash RSS

    Complex SQL query help

    982948
      Hi,

      I was wondering if I could have some help with the query I have below for this general data set, please? I need to do it in a single SQL statement. We're currently running Oracle 10g. The piece I'm struggling with is identifying that the person has all the items in a collection and to include the collection in the resulting collection of items.

      persons_items

      person     item

      Ted          cup
      Ted          saucer
      Ted          plate
      Ted          fork
      Alice          book
      Alice          thimble
      Alice          knife
      Alice          cup
      Joe          cup
      Joe          saucer
      Joe          plate
      Joe          knife
      Joe          fork
      Joe          spoon
      Jessica     spatula
      Jessica     dish



      collections

      collection_name     item

      crockery     cup
      crockery     saucer
      crockery     plate
      cutlery          knife
      cutlery          fork
      cutlery          spoon


      Query:

      What single items and collections does Ted have?     fork, crockery
      What single items and collections does Alice have?     book, thimble, knife, cup
      What single items and collections does Joe have?     crockery, cutlery
      What single items and collections does Jessica have?     spatula, dish


      Thanks in advance.

      Pat
        • 1. Re: Complex SQL query help
          Frank Kulash
          Hi,

          Welcome to the forum!

          One way to do that is to use an outer join:
          WITH     got_c_cnt    AS
          (
               SELECT     collection_name
               ,     item
               ,     COUNT (*) OVER (PARTITION BY collection_name)     AS c_cnt
               FROM    collections
          )
          SELECT DISTINCT
                    p.person
          ,       CASE
                     WHEN  COUNT (DISTINCT p.item) OVER ( PARTITION BY  p.person
                                                               ,                c.collection_name
                                         )
                            = c.c_cnt
                     THEN  c.collection_name
                     ELSE  p.item
                 END          AS collection_or_item
          FROM           persons_items  p
          FULL OUTER JOIN  got_c_cnt     c  ON  c.item  = p.item
          ORDER BY  p.person
          ,            collection_or_item
          ;
          If you'd care to post CRATE TABLE and INSERT statements for your sample data, then I could test this.
          • 2. Re: Complex SQL query help
            APC
            Wanting to do this with a single SQL statement is ambitious. No wonder you're struggling with it.

            What object types can you create (if any)?

            Are the collections a static data set? How many are there?

            Cheers, APC
            • 3. Re: Complex SQL query help
              BrendanP
              Here's one way:
              WITH person_items AS (
                      SELECT 'Ted' person, 'cup' item FROM DUAL UNION
                      SELECT 'Ted', 'saucer' FROM DUAL UNION
                      SELECT 'Ted', 'plate' FROM DUAL UNION
                      SELECT 'Ted', 'fork' FROM DUAL UNION
                      SELECT 'Alice', 'book' FROM DUAL UNION
                      SELECT 'Alice', 'thimble' FROM DUAL UNION
                      SELECT 'Alice', 'knife' FROM DUAL UNION
                      SELECT 'Alice', 'cup' FROM DUAL UNION
                      SELECT 'Joe', 'cup' FROM DUAL UNION
                      SELECT 'Joe', 'saucer' FROM DUAL UNION
                      SELECT 'Joe', 'plate' FROM DUAL UNION
                      SELECT 'Joe', 'knife' FROM DUAL UNION
                      SELECT 'Joe', 'fork' FROM DUAL UNION
                      SELECT 'Joe', 'spoon' FROM DUAL UNION
                      SELECT 'Jessica', 'spatula' FROM DUAL UNION
                      SELECT 'Jessica', 'dish' FROM DUAL
              ), collections AS (
                      SELECT 'crockery' collection_name, 'cup' item FROM DUAL UNION
                      SELECT 'crockery', 'saucer' FROM DUAL UNION
                      SELECT 'crockery', 'plate' FROM DUAL UNION
                      SELECT 'cutlery', 'knife' FROM DUAL UNION
                      SELECT 'cutlery', 'fork' FROM DUAL UNION
                      SELECT 'cutlery', 'spoon' FROM DUAL
              ), person_item_colls AS (
                      SELECT pi.person, pi.item, co.collection_name,
                             Count(*) OVER (PARTITION BY pi.person, co.collection_name) n_col
                        FROM person_items pi
                        LEFT JOIN collections co
                          ON co.item = pi.item
              )
              SELECT DISTINCT person, collection_name
                FROM person_item_colls
               WHERE collection_name IS NOT NULL
                 AND n_col > 1
               UNION ALL
               SELECT person, item
                FROM person_item_colls
               WHERE collection_name IS NULL
                 OR n_col = 1
              • 4. Re: Complex SQL query help
                BluShadow
                Expanding on Brendan's solution...

                For 11g:
                SQL> ed
                Wrote file afiedt.buf
                
                  1  WITH person_items AS (
                  2          SELECT 'Ted' person, 'cup' item FROM DUAL UNION
                  3          SELECT 'Ted', 'saucer' FROM DUAL UNION
                  4          SELECT 'Ted', 'plate' FROM DUAL UNION
                  5          SELECT 'Ted', 'fork' FROM DUAL UNION
                  6          SELECT 'Alice', 'book' FROM DUAL UNION
                  7          SELECT 'Alice', 'thimble' FROM DUAL UNION
                  8          SELECT 'Alice', 'knife' FROM DUAL UNION
                  9          SELECT 'Alice', 'cup' FROM DUAL UNION
                 10          SELECT 'Joe', 'cup' FROM DUAL UNION
                 11          SELECT 'Joe', 'saucer' FROM DUAL UNION
                 12          SELECT 'Joe', 'plate' FROM DUAL UNION
                 13          SELECT 'Joe', 'knife' FROM DUAL UNION
                 14          SELECT 'Joe', 'fork' FROM DUAL UNION
                 15          SELECT 'Joe', 'spoon' FROM DUAL UNION
                 16          SELECT 'Jessica', 'spatula' FROM DUAL UNION
                 17          SELECT 'Jessica', 'dish' FROM DUAL
                 18  ), collections AS (
                 19          SELECT 'crockery' collection_name, 'cup' item FROM DUAL UNION
                 20          SELECT 'crockery', 'saucer' FROM DUAL UNION
                 21          SELECT 'crockery', 'plate' FROM DUAL UNION
                 22          SELECT 'cutlery', 'knife' FROM DUAL UNION
                 23          SELECT 'cutlery', 'fork' FROM DUAL UNION
                 24          SELECT 'cutlery', 'spoon' FROM DUAL
                 25  ), person_item_colls AS (
                 26          SELECT pi.person, pi.item, co.collection_name,
                 27                 Count(*) OVER (PARTITION BY pi.person, co.collection_name) n_col
                 28            FROM person_items pi
                 29            LEFT JOIN collections co
                 30              ON co.item = pi.item
                 31  )
                 32  select person, listagg(collection_name,',') within group (order by collection_name) as collections
                 33  from (
                 34        SELECT DISTINCT person, collection_name
                 35        FROM   person_item_colls
                 36        WHERE  collection_name IS NOT NULL
                 37        AND    n_col > 1
                 38        UNION ALL
                 39        SELECT person, item
                 40        FROM   person_item_colls
                 41        WHERE  collection_name IS NULL
                 42        OR     n_col = 1
                 43       )
                 44* group by person
                SQL> /
                
                PERSON  COLLECTIONS
                ------- --------------------------------------------------------------------------------
                Alice   book,cup,knife,thimble
                Jessica dish,spatula
                Joe     crockery,cutlery
                Ted     crockery,fork
                For 10g:
                SQL> ed
                Wrote file afiedt.buf
                
                  1  WITH person_items AS (
                  2          SELECT 'Ted' person, 'cup' item FROM DUAL UNION
                  3          SELECT 'Ted', 'saucer' FROM DUAL UNION
                  4          SELECT 'Ted', 'plate' FROM DUAL UNION
                  5          SELECT 'Ted', 'fork' FROM DUAL UNION
                  6          SELECT 'Alice', 'book' FROM DUAL UNION
                  7          SELECT 'Alice', 'thimble' FROM DUAL UNION
                  8          SELECT 'Alice', 'knife' FROM DUAL UNION
                  9          SELECT 'Alice', 'cup' FROM DUAL UNION
                 10          SELECT 'Joe', 'cup' FROM DUAL UNION
                 11          SELECT 'Joe', 'saucer' FROM DUAL UNION
                 12          SELECT 'Joe', 'plate' FROM DUAL UNION
                 13          SELECT 'Joe', 'knife' FROM DUAL UNION
                 14          SELECT 'Joe', 'fork' FROM DUAL UNION
                 15          SELECT 'Joe', 'spoon' FROM DUAL UNION
                 16          SELECT 'Jessica', 'spatula' FROM DUAL UNION
                 17          SELECT 'Jessica', 'dish' FROM DUAL
                 18  ), collections AS (
                 19          SELECT 'crockery' collection_name, 'cup' item FROM DUAL UNION
                 20          SELECT 'crockery', 'saucer' FROM DUAL UNION
                 21          SELECT 'crockery', 'plate' FROM DUAL UNION
                 22          SELECT 'cutlery', 'knife' FROM DUAL UNION
                 23          SELECT 'cutlery', 'fork' FROM DUAL UNION
                 24          SELECT 'cutlery', 'spoon' FROM DUAL
                 25  ), person_item_colls AS (
                 26          SELECT pi.person, pi.item, co.collection_name,
                 27                 Count(*) OVER (PARTITION BY pi.person, co.collection_name) n_col
                 28            FROM person_items pi
                 29            LEFT JOIN collections co
                 30              ON co.item = pi.item
                 31  )
                 32  select person, ltrim(sys_connect_by_path(collection_name,','),',') as collections
                 33  from (
                 34        select person, collection_name, row_number() over (partition by person order by collection_name) as rn
                 35        from (
                 36              SELECT DISTINCT person, collection_name
                 37              FROM   person_item_colls
                 38              WHERE  collection_name IS NOT NULL
                 39              AND    n_col > 1
                 40              UNION ALL
                 41              SELECT person, item
                 42              FROM   person_item_colls
                 43              WHERE  collection_name IS NULL
                 44              OR     n_col = 1
                 45             )
                 46        )
                 47  where connect_by_isleaf = 1
                 48  connect by person = prior person and rn = prior rn + 1
                 49* start with rn = 1
                SQL> /
                
                PERSON  COLLECTIONS
                ------- --------------------------------------------------------------------------------
                Alice   book,cup,knife,thimble
                Jessica dish,spatula
                Joe     crockery,cutlery
                Ted     crockery,fork
                • 5. Re: Complex SQL query help
                  982948
                  Hi Gents,

                  Thanks very much to you that have replied. Below is the SQL to set up the tables and data, as was requested in one of the posts.


                  drop table person_items ;

                  drop table collections ;


                  CREATE TABLE person_items
                  (
                  "name" VARCHAR2(20 BYTE) NOT NULL ENABLE,
                  "item"      VARCHAR2(20 BYTE) NOT NULL ENABLE
                  ) ;

                  CREATE TABLE collections
                  (
                  "collection_name"      VARCHAR2(20 BYTE) NOT NULL ENABLE,
                  "item"                VARCHAR2(20 BYTE) NOT NULL ENABLE
                  ) ;


                  insert into person_items values ('Ted', 'cup') ;
                  insert into person_items values ('Ted', 'saucer') ;
                  insert into person_items values ('Ted', 'plate') ;
                  insert into person_items values ('Ted', 'fork') ;
                  insert into person_items values ('Alice', 'book') ;
                  insert into person_items values ('Alice', 'thimble') ;
                  insert into person_items values ('Alice', 'knife') ;
                  insert into person_items values ('Alice', 'cup') ;
                  insert into person_items values ('Joe', 'cup') ;
                  insert into person_items values ('Joe', 'saucer') ;
                  insert into person_items values ('Joe', 'plate') ;
                  insert into person_items values ('Joe', 'knife') ;
                  insert into person_items values ('Joe', 'fork') ;
                  insert into person_items values ('Joe', 'spoon') ;
                  insert into person_items values ('Jessica', 'spatula') ;
                  insert into person_items values ('Jessica', 'dish') ;


                  insert into collections values ('crockery', 'cup') ;
                  insert into collections values ('crockery', 'saucer') ;
                  insert into collections values ('crockery', 'plate') ;
                  insert into collections values ('cutlery', 'knife') ;
                  insert into collections values ('cutlery', 'fork') ;
                  insert into collections values ('cutlery', 'spoon') ;

                  commit ;
                  • 6. Re: Complex SQL query help
                    ascheffer
                    with persons_items
                    as ( select 'Ted' person, 'cup' item from dual
                    union all select 'Ted' person, 'saucer' item from dual
                    union all select 'Ted' person, 'plate' item from dual
                    union all select 'Ted' person, 'fork' item from dual
                    union all select 'Alice' person, 'book' item from dual
                    union all select 'Alice' person, 'thimble' item from dual
                    union all select 'Alice' person, 'knife' item from dual
                    union all select 'Alice' person, 'cup' item from dual
                    union all select 'Joe' person, 'cup' item from dual
                    union all select 'Joe' person, 'saucer' item from dual
                    union all select 'Joe' person, 'plate' item from dual
                    union all select 'Joe' person, 'knife' item from dual
                    union all select 'Joe' person, 'fork' item from dual
                    union all select 'Joe' person, 'spoon' item from dual
                    union all select 'Jessica' person, 'spatula' item from dual
                    union all select 'Jessica' person, 'dish' item from dual
                    )
                    , collections
                    as ( select 'crockery' collection_name, 'cup' item from dual
                    union all select 'crockery' collection_name, 'saucer' item from dual
                    union all select 'crockery' collection_name, 'plate' item from dual
                    union all select 'cutlery' collection_name, 'knife' item from dual
                    union all select 'cutlery' collection_name, 'fork' item from dual
                    union all select 'cutlery' collection_name, 'spoon' item from dual
                    )
                    select person
                         , listagg( item, ', ' ) within group ( order by null )
                    from (
                    select pi.person
                         , pi.item
                    from persons_items pi
                    where not exists ( select null
                                       from collections c
                                       where c.item = pi.item
                                     )              
                    union all
                    select pi.person
                         , pi.item
                    from persons_items pi
                       , collections c
                    where c.item = pi.item
                    and   exists ( select null
                                   from collections c2
                                   where c2.collection_name = c.collection_name
                                   and not exists ( select null
                                                    from persons_items pi2
                                                    where pi2.person = pi.person
                                                    and   pi2.item = c2.item
                                                  )  
                                 )
                    union all
                    select distinct pi.person
                         , c.collection_name item
                    from persons_items pi
                       , collections c
                    where c.item = pi.item
                    and   not exists ( select null
                                   from collections c2
                                   where c2.collection_name = c.collection_name
                                   and not exists ( select null
                                                    from persons_items pi2
                                                    where pi2.person = pi.person
                                                    and   pi2.item = c2.item
                                                  )  
                                 )
                    )
                    group by person
                    • 7. Re: Complex SQL query help
                      Frank Kulash
                      Hi,
                      979945 wrote:
                      Hi Gents,

                      Thanks very much to you that have replied. Below is the SQL to set up the tables and data, as was requested in one of the posts.
                      Thanks! That helps the people who want to help you understand the problem better, and helps them post solutions that really work.

                      The forum FAQ {message:id=9360002} has many tips that can help you.
                      drop table person_items ;

                      drop table collections ;


                      CREATE TABLE person_items
                      (
                      "name" VARCHAR2(20 BYTE) NOT NULL ENABLE,
                      "item"      VARCHAR2(20 BYTE) NOT NULL ENABLE
                      ) ;
                      Remember that anything insiode quotes is case-sensitive. Using lower-case letters in column names (or other identifiers) is a lot of trouble. Do you really need non-standard names like that? I suggest you lose the double-quotes.
                      CREATE TABLE collections
                      (
                      "collection_name"      VARCHAR2(20 BYTE) NOT NULL ENABLE,
                      "item"                VARCHAR2(20 BYTE) NOT NULL ENABLE
                      ) ;


                      insert into person_items values ('Ted', 'cup') ;
                      insert into person_items values ('Ted', 'saucer') ;
                      insert into person_items values ('Ted', 'plate') ;
                      insert into person_items values ('Ted', 'fork') ;
                      insert into person_items values ('Alice', 'book') ;
                      insert into person_items values ('Alice', 'thimble') ;
                      insert into person_items values ('Alice', 'knife') ;
                      insert into person_items values ('Alice', 'cup') ;
                      insert into person_items values ('Joe', 'cup') ;
                      insert into person_items values ('Joe', 'saucer') ;
                      insert into person_items values ('Joe', 'plate') ;
                      insert into person_items values ('Joe', 'knife') ;
                      insert into person_items values ('Joe', 'fork') ;
                      insert into person_items values ('Joe', 'spoon') ;
                      insert into person_items values ('Jessica', 'spatula') ;
                      insert into person_items values ('Jessica', 'dish') ;


                      insert into collections values ('crockery', 'cup') ;
                      insert into collections values ('crockery', 'saucer') ;
                      insert into collections values ('crockery', 'plate') ;
                      insert into collections values ('cutlery', 'knife') ;
                      insert into collections values ('cutlery', 'fork') ;
                      insert into collections values ('cutlery', 'spoon') ;

                      commit ;
                      There seems to be some disagreement about your requirements. It would help if you included an example of someone who had more than 1, but less that all, of the items in a collection. For example, if you add this to the sample data above:
                      insert into person_items values ('Ted', 'spoon') ;
                      Now Ted has 2 out of 3 items in the cutlery collection. The results from the query I posted earlier are:
                      NAME                 COLLECTION_OR_ITEM
                      -------------------- --------------------
                      Alice                book
                      Alice                cup
                      Alice                knife
                      Alice                thimble
                      Jessica              dish
                      Jessica              spatula
                      Joe                  crockery
                      Joe                  cutlery
                      Ted                  crockery
                      Ted                  fork
                      Ted                  spoon
                      Brendan's solution gets the following results for Ted
                      NAME                 COLLECTION_NAME
                      -------------------- --------------------
                      ...
                      Ted                  crockery
                      Ted                  cutlery
                      • 8. Re: Complex SQL query help
                        BrendanP
                        Actually, I interpreted the requirement as to give the collection name whenever more than one item was present, based on the wording 'What single items...', but I can see that that contradicts the first paragraph. Anyway, a small revision gives the same result as your revised query with the extra record:
                        WITH person_items AS (
                                SELECT 'Ted' person, 'cup' item FROM DUAL UNION
                                SELECT 'Ted', 'saucer' FROM DUAL UNION
                                SELECT 'Ted', 'plate' FROM DUAL UNION
                                SELECT 'Ted', 'fork' FROM DUAL UNION
                                SELECT 'Ted', 'spoon' FROM DUAL UNION
                                SELECT 'Alice', 'book' FROM DUAL UNION
                                SELECT 'Alice', 'thimble' FROM DUAL UNION
                                SELECT 'Alice', 'knife' FROM DUAL UNION
                                SELECT 'Alice', 'cup' FROM DUAL UNION
                                SELECT 'Joe', 'cup' FROM DUAL UNION
                                SELECT 'Joe', 'saucer' FROM DUAL UNION
                                SELECT 'Joe', 'plate' FROM DUAL UNION
                                SELECT 'Joe', 'knife' FROM DUAL UNION
                                SELECT 'Joe', 'fork' FROM DUAL UNION
                                SELECT 'Joe', 'spoon' FROM DUAL UNION
                                SELECT 'Jessica', 'spatula' FROM DUAL UNION
                                SELECT 'Jessica', 'dish' FROM DUAL
                        ), collections AS (
                                SELECT 'crockery' collection_name, 'cup' item FROM DUAL UNION
                                SELECT 'crockery', 'saucer' FROM DUAL UNION
                                SELECT 'crockery', 'plate' FROM DUAL UNION
                                SELECT 'cutlery', 'knife' FROM DUAL UNION
                                SELECT 'cutlery', 'fork' FROM DUAL UNION
                                SELECT 'cutlery', 'spoon' FROM DUAL        -- End of data setup
                        ), coll_counts AS (
                                SELECT collection_name, item, Count(*) OVER (PARTITION BY collection_name) coll_size
                                  FROM collections
                        ), person_item_colls AS (
                                SELECT pi.person, pi.item, co.collection_name,
                                       Count(*) OVER (PARTITION BY pi.person, co.collection_name) - Nvl (co.coll_size, 0) col_diff
                                  FROM person_items pi
                                  LEFT JOIN coll_counts co
                                    ON co.item = pi.item
                        )
                        SELECT DISTINCT person, collection_name
                          FROM person_item_colls
                         WHERE col_diff = 0
                         UNION ALL
                         SELECT person, item
                          FROM person_item_colls
                         WHERE col_diff != 0
                        • 9. Re: Complex SQL query help
                          Peter vd Zwan
                          Hi,

                          Try this:
                          WITH person_items AS (
                                  SELECT 'Ted' person, 'cup' item FROM DUAL UNION
                                  SELECT 'Ted', 'saucer' FROM DUAL UNION
                                  SELECT 'Ted', 'plate' FROM DUAL UNION
                                  SELECT 'Ted', 'fork' FROM DUAL UNION
                                  SELECT 'Alice', 'book' FROM DUAL UNION
                                  SELECT 'Alice', 'thimble' FROM DUAL UNION
                                  SELECT 'Alice', 'knife' FROM DUAL UNION
                                  SELECT 'Alice', 'cup' FROM DUAL UNION
                                  SELECT 'Joe', 'cup' FROM DUAL UNION
                                  SELECT 'Joe', 'saucer' FROM DUAL UNION
                                  SELECT 'Joe', 'plate' FROM DUAL UNION
                                  SELECT 'Joe', 'knife' FROM DUAL UNION
                                  SELECT 'Joe', 'fork' FROM DUAL UNION
                                  SELECT 'Joe', 'spoon' FROM DUAL UNION
                                  SELECT 'Jessica', 'spatula' FROM DUAL UNION
                                  SELECT 'Jessica', 'dish' FROM DUAL
                          ), collections AS (
                                  SELECT 'crockery' collection_name, 'cup' item FROM DUAL UNION
                                  SELECT 'crockery', 'saucer' FROM DUAL UNION
                                  SELECT 'crockery', 'plate' FROM DUAL UNION
                                  SELECT 'cutlery', 'knife' FROM DUAL UNION
                                  SELECT 'cutlery', 'fork' FROM DUAL UNION
                                  SELECT 'cutlery', 'spoon' FROM DUAL
                          )
                          , col_count as
                          (
                          select
                            collection_name
                            ,count(item) item_count
                          
                          from
                            collections
                          
                          group by
                            collection_name
                          )
                          ,person_collection_count as
                          (select
                            pi.person
                            ,pi.item
                            ,c.collection_name
                            ,count(pi.item) over (partition by pi.person, c.collection_name order by null rows between unbounded preceding and unbounded following) pic
                            ,cc.item_count
                          
                          from
                            person_items      pi    join
                            collections       c     on (pi.item = c.item) join
                            col_count         cc    on (c.collection_name = cc.collection_name)
                          )
                          ,person_collectoins_items as
                          (
                          select distinct
                            pcc.person
                            ,collection_name
                          
                          from
                            person_collection_count   pcc
                          
                          where
                            pic = item_count
                          union all
                          select
                            pi.person
                            ,pi.item
                          
                          from
                            person_items              pi    left outer join
                            person_collection_count   pcc   on (pi.item = pcc.item and pi.person = pcc.person and pcc.pic = pcc.item_count)
                          
                          where
                            pcc.item is null
                          )
                          
                          select
                            person
                            ,listagg(collection_name,',') within group (order by collection_name) col_item
                          
                          from
                            person_collectoins_items
                          
                          group by
                            person
                          ;
                          PERSON  COL_ITEM
                          ------- --------------------------------------------
                          Alice   book,cup,knife,thimble
                          Jessica dish,spatula
                          Joe     crockery,cutlery
                          Ted     crockery,fork
                          Regards,

                          Peter
                          • 10. Re: Complex SQL query help
                            982948
                            /*

                            Again thank you all for putting what looks like a great deal of effort into this.


                            To answer the question by Frank Kulash:

                            'There seems to be some disagreement about your requirements. It would help if
                            you included an example of someone who had more than 1, but less that all, of the items in a collection?'


                            the person should have all items in a collection present to be deemed to have the collection. Odd items for the person
                            either not in any collection or not part of a full collection of items for them, should be returned on their own. For example, Ted's
                            fork and spoon as they don't make up a full cutlery set for Ted. I've included it in the DDL below.

                            What single items and collections does Ted have?      fork, spoon , crockery,
                            What single items and collections does Alice have?      book, thimble, knife, cup
                            What single items and collections does Joe have?      crockery, cutlery
                            What single items and collections does Jessica have?      spatula, dish

                            Also, all the queries submitted using the WITH clause seem to manipulate the data to produce another (sometimes more than 1) list
                            first, 'person_item_colls' in BrendanP's reply and 'col_count', 'person_collectoins_items', etc. in Peter vZwan's reply
                            and 'got_c_cnt' in Frank's. ascheffer doesn't seem to and looks like a union of 2 divide statements and a select with no prelist
                            being generated before the main select is executed.

                            This prelisting would need to be done at runtime, is that a reasonable thing to want to do or is there no difference than if there's
                            none done like in ascheffer's example?

                            */

                            drop table person_items ;

                            drop table collections ;


                            CREATE TABLE person_items
                            (
                            name     VARCHAR2(20 BYTE) NOT NULL ENABLE,
                            item      VARCHAR2(20 BYTE) NOT NULL ENABLE
                            ) ;

                            CREATE TABLE collections
                            (
                            collection_name      VARCHAR2(20 BYTE) NOT NULL ENABLE,
                            item                VARCHAR2(20 BYTE) NOT NULL ENABLE
                            ) ;


                            insert into person_items values ('Ted', 'cup') ;
                            insert into person_items values ('Ted', 'saucer') ;
                            insert into person_items values ('Ted', 'plate') ;
                            insert into person_items values ('Ted', 'fork') ;
                            insert into person_items values ('Ted', 'spoon') ;

                            insert into person_items values ('Alice', 'book') ;
                            insert into person_items values ('Alice', 'thimble') ;
                            insert into person_items values ('Alice', 'knife') ;
                            insert into person_items values ('Alice', 'cup') ;

                            insert into person_items values ('Joe', 'cup') ;
                            insert into person_items values ('Joe', 'saucer') ;
                            insert into person_items values ('Joe', 'plate') ;
                            insert into person_items values ('Joe', 'knife') ;
                            insert into person_items values ('Joe', 'fork') ;
                            insert into person_items values ('Joe', 'spoon') ;

                            insert into person_items values ('Jessica', 'spatula') ;
                            insert into person_items values ('Jessica', 'dish') ;


                            insert into collections values ('crockery', 'cup') ;
                            insert into collections values ('crockery', 'saucer') ;
                            insert into collections values ('crockery', 'plate') ;

                            insert into collections values ('cutlery', 'knife') ;
                            insert into collections values ('cutlery', 'fork') ;
                            insert into collections values ('cutlery', 'spoon') ;
                            • 11. Re: Complex SQL query help
                              Frank Kulash
                              Hi,
                              979945 wrote:
                              the person should have all items in a collection present to be deemed to have the collection. Odd items for the person
                              either not in any collection or not part of a full collection of items for them, should be returned on their own. For example, Ted's
                              fork and spoon as they don't make up a full cutlery set for Ted. I've included it in the DDL below.
                              Okay; the query I suggested does that, and Brendan's approach can do that too, as Brendan showed.
                              Also, all the queries submitted using the WITH clause seem to manipulate the data to produce another (sometimes more than 1) list
                              first, 'person_item_colls' in BrendanP's reply and 'col_count', 'person_collectoins_items', etc. in Peter vZwan's reply
                              and 'got_c_cnt' in Frank's. ascheffer doesn't seem to and looks like a union of 2 divide statements and a select with no prelist
                              being generated before the main select is executed.

                              This prelisting would need to be done at runtime, is that a reasonable thing to want to do or is there no difference than if there's
                              none done like in ascheffer's example?
                              Sorry, I don't understand the issue here.
                              WITH clauses work in Oracle 9.1 and up. An in-line view can always be replaced by a WITH clause. That is:
                              SELECT  a, b c
                              FROM    (
                                          SELECT  x AS a, b, c
                                          FROM    ...
                                      ) sub_q
                              WHERE   ...
                              is equivalent to
                              WITH     sub_q     AS
                              (
                                      SELECT  x AS a, b, c
                                      FROM    ...
                              )
                              SELECT  a, b c
                              FROM    sub_q
                              WHERE   ...
                              If a WITH clause is only referenced in one place (which is the case with the WITH clauses Brendan and I suggested), then it can be replaced by an in-line view. Usaully, there's no difference in performance. Either way, the sub-query is part of the query, and both are executed at the same time. (When else could they possibly be executed?) What do you mean by "prelisting"?

                              Brendan and I chose to use WITH clauses. We could have used in-line views, but we thought it was better to use WITH clauses. It's just a matter of style. Ascheffer chose to use an in-line view. he could have used a WITH clause, but thought it was better to use an in-line view. It's just a matter of style.

                              Some front-ends have trouble with WITH clauses. If you can't use a WITH clause for some reason, it could be because you are using an extremely old front end (such as SQL*Plus version 8). If that's the case, and you can't upgrade, there are ways to hide the WITH clause so the front end won't complain, or (in this problem, and most others) you can re-write the query to use in-line views instead of a WITH clause.