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:
  `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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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?
Russ SuterCommented:
Will the field have a consistent "default value"? If so you can use COALESCE(NULLIF([some condition]), ...)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
select coalesce(,,,
        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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mankowitzAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.