Hi,

I just can not find the set based sql approach to the following
application logic:

I have a table of choices with id and text, say:
1 coffee
2 tea
3 water
4 juice

Now some users have restrictions, for example user 1 is
disallowed coffeine. Some Users are forced one choice - user 2 is
only allowed water, so there is another type of restriction.

This would yield table choicerestrictions (userid, choiceid, tor):
1 2 exclude
1 2 exclude
2 3 force

Is this a known application pattern I can read about somewhere?

How could I get a result set in one select? This would be far
better than doing it in the application.

Table design: the force entry in choicerestrictions would be
equivalent to exclude choices 1,2 and 4. I prefer a force entry,
because it states the logic, and is better when you insert 5 milk
into choices.

Thanks for your comments.

/Str.

M. Strobel wrote:
Here is DDL for your example:

CREATE TABLE users (
userid integer PRIMARY KEY,
username text NOT NULL
);

INSERT INTO users VALUES (1, 'one');
INSERT INTO users VALUES (2, 'two');
INSERT INTO users VALUES (3, 'three');

CREATE TABLE choices (
choiceid integer PRIMARY KEY,
choicename text NOT NULL
);

INSERT INTO choices VALUES (1, 'coffee');
INSERT INTO choices VALUES (2, 'tea');
INSERT INTO choices VALUES (3, 'water');
INSERT INTO choices VALUES (4, 'juice');

CREATE TYPE restriction AS ENUM ('exclude', 'force');

CREATE TABLE choicerestrictions (
userid integer NOT NULL REFERENCES users(userid),
choiceid integer NOT NULL REFERENCES choices(choiceid),
tor restriction NOT NULL,
PRIMARY KEY (userid, choiceid)
);

INSERT INTO choicerestrictions VALUES (1, 1, 'exclude');
INSERT INTO choicerestrictions VALUES (1, 2, 'exclude');
INSERT INTO choicerestrictions VALUES (2, 3, 'force');


A valid way of selecting all possible (username, choicename)
pairs would be:

(SELECT u.username, c.choicename
FROM (SELECT username, r.choiceid
FROM choices c
JOIN choicerestrictions r ON
(c.choiceid = r.choiceid AND r.tor = 'force')
RIGHT OUTER JOIN users USING (userid)
) u
JOIN choices c ON
(u.choiceid = c.choiceid OR u.choiceid IS NULL))
EXCEPT (SELECT u.username, c.choicename
FROM users u
JOIN choicerestrictions r USING (userid)
JOIN choices c USING (choiceid)
WHERE tor = 'exclude');

I don't claim that it is the most efficient way possible...

Yours,
Laurenz Albe

Am 09.09.2011 11:02, schrieb Laurenz Albe:
Thank you, it works fine. I should have included the ddl

/Str.

Am 09.09.2011 11:02, schrieb Laurenz Albe:
Hey, it mimics a procedural approach. I would have selected the
force entry, if there is none select all choices omitting the
exclude-entries.

Just could not get it into one query.

/Str.