Re: Effective query for listing flags in use by messages in a folder

På onsdag 18. mars 2015 kl. 12:07:00, skrev Hector Vass <
hector(dot)vass(at)metametrics(dot)co(dot)uk <mailto:hector(dot)vass(at)metametrics(dot)co(dot)uk>>: Andreas
 ... your code and one of my examples ...  I have modified my option 2 to give
an example with data that gives you I believe exactly the same output (one row
for each flag set for folder_id=3 with the text representation of the
flag) ... when you satisfy yourself this produces the same results you might
then want to go back and re-read my original post which rather than feeding
 you verbatim how to produce exactly the same results gave the the pro's and
con's of 3x different approaches... I chose to illustrate my option 2 because
it is easy to understand and is a reasonable production solution, option 1 was
really just to get you thinking differently about how to do this and option 3 I
concede was more advanced and probably but requires skills other than plain SQL
to implement.

  It's not that I didn't read you post, I just don't see how it solves the
problem of listing a distinct set of flags being set on messages in a folder.
AFAICS your examples list messages with any or a specific set of flags set,
which is not what I'm after.   I see now that I didn't specify the "msg"-column
so maybe it wasn't clear that the there's only one tuple in "message" for each
message and a message may have several flags set.   This is a more realistic
table, with "msg" as varchar holding the actual text of the message:   create
table message( folder_id integer not NULL, msg varchar NOT NULL, is_seen
boolean NOT NULL default false, is_replied boolean not null default false,
is_forwardedboolean not null default false, is_deleted boolean not null default
false, is_draft boolean not null default false, is_flagged boolean not null
default false);   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47
909 56 963 andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com> www.visena.com
<https://www.visena.com> <https://www.visena.com>