I have a database which stores simple text expansions. For example, the user enters a shortcut, and the phrase_text is visible. For the system default shortcuts, the username and group fields are null. If the phrase belongs to a group, then groupname will be populated. If it belongs to a user, the username field will be populated.
I want a query which will find all the shortcuts for a given user, knowing that if the user has any preferences, they will supersede the group preferences, which in turn supersede the system preferences.
CREATE TABLE IF NOT EXISTS `phrases2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`shortcut` varchar(20) NOT NULL,
`username` varchar(10) DEFAULT NULL,
`groupname` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
INSERT INTO `phrases2` (`id`, `shortcut`, `username`, `groupname`, `phrase_text`) VALUES
(1, 'a', NULL, NULL, 'AAA'),
(3, 'b', NULL, NULL, 'BBB'),
(4, 'c', NULL, NULL, 'CCC'),
(5, 'a', 'user1', NULL, 'AAA - for user 1'),
(6, 'a', NULL, 'group1', 'AAA - group1'),
(7, 'a', 'user2', NULL, 'AAA - for user 2'),
(8, 'a', NULL, 'group2', 'AAA - group2');
Suppose I want all the phrases for user1. I'd return rows 3,4,5 becuase row 1 is superseded by row 5.
Or, suppose I want all the phrases for user1 who also happens to be in group2. I'd still get rows 3,4,5 because user preference outranks group preference, which outranks system default preference.
Here is an SQL fiddle