Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Need help with a query that returns items that have 0 or 1 of a category

    Hi,
    I want to query all items that have zero or one of a particular category type.
    Code:
    item.item_id  item.name  category.type category.name
    1             'item1'    'group'       'A'
    1             'item1'    'rate'        'X'
    1             'item1'    'rate'        'Y'
    2             'item2'    'group'       'A'
    2             'item2'    'group'       'B'
    3             'item3'    'rate'        'Y'
    4             null       null          null
    E.g, given the above, I want to query all items that have a category with type="group" and name="B", or have no "group" category.
    This should return items 2, 3, 4.

    The tables are defined as follows:
    Code:
    CREATE TABLE item (
      item_id int NOT NULL,
      name varchar(10) NOT NULL,
      PRIMARY KEY (item_id)
    );
    
    CREATE TABLE category (
      category_id int NOT NULL,
      name varchar(10) NOT NULL,
      type varchar(10) NOT NULL,
      PRIMARY KEY (category_id)
    );
    
    CREATE TABLE item_category (
      item_id int  NOT NULL,
      category_id int NOT NULL,
      PRIMARY KEY (item_id,category_id)
    );
    And the sample data:
    Code:
    INSERT INTO category VALUES (1,'A','group');
    INSERT INTO category VALUES (2,'B','group');
    INSERT INTO category VALUES (3,'X','rate');
    INSERT INTO category VALUES (4,'Y','rate');
    
    INSERT INTO item VALUES (1,'item1');
    INSERT INTO item VALUES (2,'item2');
    INSERT INTO item VALUES (3,'item3');
    INSERT INTO item VALUES (4,'item4');
    
    INSERT INTO item_category VALUES (1,1);
    insert into item_category values (1,3);
    insert into item_category values (1,4);
    insert into item_category values (2,1);
    insert into item_category values (2,2);
    insert into item_category values (3,4);
    The following query gets part way there. It doesn't handle the "item4" case, and seems way to complex.

    Code:
    SELECT i.item_id, i.name, c.type, c.name
    FROM   item i
           JOIN item_category ic
             ON ic.item_id = i.item_id
           JOIN category c
             ON ic.category_id = c.category_id
    WHERE  c.TYPE = "group"
           AND c.name = "B"
            OR i.item_id IN (SELECT i.item_id
                             FROM   item i
                                    LEFT JOIN item_category ic
                                      ON ic.item_id = i.item_id
                                    LEFT JOIN category c
                                      ON ic.category_id = c.category_id
                             WHERE  ( c.TYPE <> "group"
                                       OR c.TYPE IS NULL )
                                    AND i.item_id NOT IN (SELECT i.item_id
                                                          FROM   item i
                                                                 JOIN item_category ic
                                                                   ON ic.item_id = i.item_id
                                                                 JOIN category c
                                                                   ON ic.category_id = c.category_id
                                                          WHERE  c.TYPE = "group"
                                                                 AND c.name <> "B"))
    Thanks

    PS: I don't want to use unions as its not supported by the db abstraction layer.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This may be better achieved with UNION
    Code:
    SELECT  item.item_id
          , item.name
          , category.type
          , category.name
    FROM    item
    LEFT OUTER JOIN 
            item_category
    ON  item_category.item_id   = item.item_id
    LEFT OUTER JOIN 
            category
    ON  category.category_id    = item_category.category_id
    WHERE   NOT EXISTS  (
                            SELECT  * 
                            FROM    item_category   AS ic 
                            INNER JOIN 
                                    category        AS c 
                            ON  c.category_id       = ic.category_id 
                            WHERE   c.type          = 'Group' 
                                AND ic.item_id      = item.item_id
                        )
        OR  
            (
                category.type       = 'Group'
            AND
                category.name       = 'B'
            )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by b.wildered View Post
    PS: I don't want to use unions as its not supported by the db abstraction layer.
    Oh ok - I guess not. That's a nasty restriction of the abstraction layer.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Alternative:
    Code:
    SELECT  item.item_id
          , item.name
          , type            = MAX(category.type)
          , name            = MAX(category.name)
    FROM    item
    LEFT OUTER JOIN 
            item_category
    ON  item_category.item_id   = item.item_id
    LEFT OUTER JOIN 
            category
    ON  category.category_id    = item_category.category_id
    GROUP BY 
            item.item_id
          , item.name
    HAVING  SUM(CASE WHEN category.type = 'Group'                           THEN 937    ELSE 0 END) = 0     --There are no Rudys
        OR  SUM(CASE WHEN category.type = 'Group' AND category.name = 'B'   THEN 900713 ELSE 0 END) = 900713--There is only one pootle
    Last edited by pootle flump; 07-09-10 at 05:23.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by pootle flump View Post
    type = MAX(category.type)
    What kind of syntax is that? Never seen this type of column aliasing (if it is one)

    Quote Originally Posted by pootle flump
    That's a nasty restriction of the abstraction layer.
    I'd say a ridiculous restriction

    But as far as I can tell, your alternative query returs item_id = 1 as well.

    What about
    Code:
    SELECT  i.item_id, i.name, c.type, c.name
    FROM item i 
      LEFT JOIN item_category ic ON i.item_id = ic.item_id
      LEFT JOIN category c ON ic.category_id = c.category_id
    WHERE (c.type = 'group' AND c.name = 'B') or c.type is null
       OR i.item_id in (SELECT i.item_id
                        FROM item i 
                          LEFT JOIN item_category ic ON i.item_id = ic.item_id
                          LEFT JOIN category c ON ic.category_id = c.category_id
                        GROUP BY i.item_id
                        HAVING sum(CASE WHEN c.type = 'group' THEN 1 ELSE 0 END) = 0);

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by shammat View Post
    What kind of syntax is that? Never seen this type of column aliasing (if it is one)
    Correct - it is SQL Server not ANSI. My mistake.
    Code:
     MAX(category.type) AS type
    Quote Originally Posted by shammat View Post
    I'd say a ridiculous restriction
    Agreed.

    Quote Originally Posted by shammat View Post
    But as far as I can tell, your alternative query returs item_id = 1 as well.
    Incorrect - run it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by pootle flump View Post
    Incorrect - run it.
    Code:
    c:\Projects\mgm-rnd\Datenbanken>psql 
    psql (8.4.3)
    Type "help" for help.
    
    postgres=> SELECT  item.item_id
    postgres->       , item.name
    postgres->       , MAX(category.type)
    postgres->       , MAX(category.name)
    postgres-> FROM    item
    postgres-> LEFT OUTER JOIN
    postgres->         item_category
    postgres-> ON  item_category.item_id   = item.item_id
    postgres-> LEFT OUTER JOIN
    postgres->         category
    postgres-> ON  category.category_id    = item_category.category_id
    postgres-> GROUP BY
    postgres->         item.item_id
    postgres->       , item.name
    postgres-> HAVING  SUM(CASE WHEN category.type = 'Group'                           THEN 937    ELSE 0 END) = 0
    postgres->     OR  SUM(CASE WHEN category.type = 'Group' AND category.name = 'B'   THEN 900713 ELSE 0 END) = 900713;
     item_id | name  |  max  | max
    ---------+-------+-------+-----
           1 | item1 | rate  | Y
           2 | item2 | group | B
           4 | item4 |       |
           3 | item3 | rate  | Y
    (4 rows)
    
    
    postgres=>
    The tables have been prepared with the sample code above.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's interesting. You have SQL Server right? Run it on there - it works fine.

    This looks like a bug in postgres. For the first condition:
    Any row with 'group' in is assigned a non zero score. These scores are summed by item and non zero scores removed. Item one has one 'group' so the sum of the score is non zero.
    Any row with 'group' and 'B' is assigned a non zero score. These scores are summed by item and any sum not equalling the assigned non zero score are removed. Item 1 has no rows meeting the condition so the sum of the score is zero.

    Can you explain how item 1 is being returned?
    Last edited by pootle flump; 07-09-10 at 09:14.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In fact - what does this return:
    Code:
    SELECT  item.item_id
          , item.name
          , category.type           
          , category.name          
          , CASE WHEN category.type = 'Group'                           THEN 937    ELSE 0 END AS rudys_test
          , CASE WHEN category.type = 'Group' AND category.name = 'B'   THEN 900713 ELSE 0 END AS pootles_test
          FROM    item
    LEFT OUTER JOIN 
            item_category
    ON  item_category.item_id   = item.item_id
    LEFT OUTER JOIN 
            category
    ON  category.category_id    = item_category.category_id
    In SQL Server I get:
    Code:
    item_id     name    type    name    rudys_test  pootles_test
    -------------------------------------------------------------
    1           item1   group   A       937         0
    1           item1   rate    X       0           0
    1           item1   rate    Y       0           0
    2           item2   group   A       937         0
    2           item2   group   B       937         900713
    3           item3   rate    Y       0           0
    4           item4   NULL    NULL    0           0
    Last edited by pootle flump; 07-09-10 at 09:14.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by pootle flump View Post
    That's interesting. You have SQL Server right? Run it on there - it works fine.
    I get the same result with SQL Server as with PostgreSQL

    But I found the reason: PostgreSQL's character comparison is case-sensitive - as is my SQL Server installation.

    So the condition CASE WHEN category.type = 'Group' fails because I inserted everything in lower case (because I simply did a copy & paste with the sample statements)

    I didn't see this when I ran the statement the first time.

    When I change 'Group' to 'group' in your queries everything is fine.

    Sorry for the confusion.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I ran mine case insensitive (as you might imagine). I should apologise - I was the less precise.

    I have noticed also that mine will fail if the OP does not enforce the natural keys or has over-abstracted his\ her problem.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    You have SQL Server right? Run it on there - it works fine.
    quote. of. the. year.

    would make an awfully nice sig line, too

    oh, and poots, thanks for the mention in one of your sort constants

    to b.wildered, i just have to say, best user name ever!


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My pleasure, though my prevalent motivation was to write SQL that stated "there can only be one pootle".

    b.wildered - second best user name ever!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jul 2010
    Posts
    4
    Quote Originally Posted by shammat View Post
    What kind of syntax is that? Never seen this type of column aliasing (if it is one)

    I'd say a ridiculous restriction

    But as far as I can tell, your alternative query returs item_id = 1 as well.

    What about
    Code:
    SELECT  i.item_id, i.name, c.type, c.name
    FROM item i 
      LEFT JOIN item_category ic ON i.item_id = ic.item_id
      LEFT JOIN category c ON ic.category_id = c.category_id
    WHERE (c.type = 'group' AND c.name = 'B') or c.type is null
       OR i.item_id in (SELECT i.item_id
                        FROM item i 
                          LEFT JOIN item_category ic ON i.item_id = ic.item_id
                          LEFT JOIN category c ON ic.category_id = c.category_id
                        GROUP BY i.item_id
                        HAVING sum(CASE WHEN c.type = 'group' THEN 1 ELSE 0 END) = 0);
    Actually there are a few more ridiculous restrictions:
    • the db layer uses hibernate HQL which doesn't support UNION
    • the db layer doesn't (yet) support CASE, GROUP BY, HAVING or sub selects

    In any case, the solutions look workable. Just a matter of finding one which can be mapped to HQL, and then adding support for it.

    Thanks very much for your efforts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •