On 11.02.2008 00:29 CE(S)T, Peter Brawley wrote:
message (messageID)
keylist (keylistID)
tag ( tagID, readaccesskeylist references keylist(keylistID) )
message_revision_tag ( ???, messageID references message(messageID),
tagID references tag(tagID))
Another table:
message_revision(MessageId references message, RevisionNumber)
Correction:
message_revision_tag(MessageId, RevisionNumber, TagId)
(MessageId, RevisionNumber) references message_revision
(i) Finding messages which have a deny-access tag looks like a simple join:
SELECT DISTINCT messageID
FROM message_revision_tag AS mrt
JOIN tag AS t ON mrt.tagID=t.tagID
WHERE t.readaccesskeylist IS NOT NULL;
(ii) We get the messages not in the above result with a simple exclusion
join:
SELECT messageID
FROM message m
LEFT JOIN (
SELECT DISTINCT messageID
FROM message_revision_tag AS mrt
JOIN tag AS t ON mrt.tagID=t.tagID
WHERE t.readaccesskeylist IS NOT NULL
) AS banned USING (messageID)
WHERE banned.messageID IS NULL,
Or did I miss something?
I'm afraid I cannot integrate this in my large query. It looks too
simple and I don't know where to put its parts. Maybe I'll really have
to show the full schema and the complete query...
It's not only that my entire query will find messages that have no tag
with a ReadAccessKeylist assigned; it will rather find messages that
have no tag with a keylist which does not include the currently logged
in user's UserId or one of this user's additional keys, which are again
stored in a keylist.
--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
