Select values from a table which may supersede each other

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.

Example data:
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,
  `phrase_text` text,
  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');

Open in new window


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
LVL 24
mankowitzAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
select coalesce(usr.id, grp.id, def.id),
        def.shortcut,
        coalesce(usr.phrase_text, grp.phrase_text, def.phrase_text)
 from      (select * from phrases2 where username is null and groupname is null) def
 left join (select * from phrases2 where groupname = 'group1') grp on grp.shortcut = def.shortcut
 left join (select * from phrases2 where username  = 'user1' ) usr on usr.shortcut = def.shortcut

Open in new window

will return IDs 3, 4, 5, using the techniques as stated above, with the reasonable constraints as stated.
0
 
Russ SuterCommented:
Assuming your heirarchy is static, that is to say that you will only ever have a user, group, system order in which no object can belong to more than one parent type, then you could just use a LEFT JOIN for each level and use the COALESCE() function to return the first non-null value.
0
 
mankowitzAuthor Commented:
You are correct about the uniqueness. Assume the following constraint:

CONSTRAINT unique_phrase UNIQUE (shortcut, username, groupname)

Open in new window


But the phrase_text is the field that I need, and it will never be null. How could I use coalesce for that?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Russ SuterCommented:
Will the field have a consistent "default value"? If so you can use COALESCE(NULLIF([some condition]), ...)
0
 
mankowitzAuthor Commented:
Can you give a working example?

Here is what I have now, but it feels terribly inefficient:

select shortcut, phrase_text from phrases2 where username='user1'
union select shortcut, phrase_text from phrases2 where groupname='group1' 
    and shortcut not in (select shortcut from phrases2 where username='user1')
union select shortcut, phrase_text from phrases2 where username is null and groupname is null 
    and shortcut not in (select shortcut from phrases2 where username='user1' or groupname='group1')

Open in new window

0
 
mankowitzAuthor Commented:
great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.