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

b.wildered · #1 (**permalink**) 07-09-10, 02:20

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.

pootle flump · #2 (**permalink**) 07-09-10, 04:08

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'
        )

pootle flump · #3 (**permalink**) 07-09-10, 04:09

Quote:

Originally Posted by b.wildered

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.

pootle flump · #4 (**permalink**) 07-09-10, 04:18

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

shammat · #5 (**permalink**) 07-09-10, 06:47

Quote:

Originally Posted by pootle flump

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);

pootle flump · #6 (**permalink**) 07-09-10, 06:58

Quote:

Originally Posted by shammat

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

I'd say a ridiculous restriction

Agreed.

Quote:

Originally Posted by shammat

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

Incorrect - run it.

shammat · #7 (**permalink**) 07-09-10, 07:10

Quote:

Originally Posted by pootle flump

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.

pootle flump · #8 (**permalink**) 07-09-10, 08:04

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?

pootle flump · #9 (**permalink**) 07-09-10, 08:06

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

shammat · #10 (**permalink**) 07-09-10, 08:23

Quote:

Originally Posted by pootle flump

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.

pootle flump · #11 (**permalink**) 07-09-10, 08:26

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.

r937 · #12 (**permalink**) 07-09-10, 09:51

Quote:

Originally Posted by pootle flump

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!

pootle flump · #13 (**permalink**) 07-09-10, 10:17

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!

b.wildered · #14 (**permalink**) 07-09-10, 22:46

Quote:

Originally Posted by shammat

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech