Expanding a working query without adding multiple join statements?

I have the following tables set up:

t_users table:

|----------------------------------|
|uid | name | email |
|----------------------------------|
|1 | Maria | maria@example.com |
|2 | George | george@example.com |
|----------------------------------|


t_settings table:

|---------------------------------|
|t_sid | name | default_value |
|---------------------------------|
|1 | uicolour | #f00 |
|2 | language | en |
|---------------------------------|


t_users_settings table:

|--------------------|
|uid | t_sid | value |
|--------------------|
|2 | 1 | #000 |
|1 | 2 | es |
|--------------------|


What I want, is for each user, to be able to pull all of their custom settings, and also pull the default settings for anything that they haven't changed. I have the following query that works:

SELECT t_users., t_users_settings., t_settings.*
FROM t_users
LEFT JOIN t_users_settings ON t_users.uid = t_users_settings.t_uid
LEFT JOIN t_settings ON t_settings.t_sid != t_users_settings.t_sid
WHERE t_users_settings.t_sid IS NOT NULL
AND t_users_settings.t_uid IS NOT NULL
AND t_users.uid = '';


However, it appears that if I add more settings (Such as country), I then have to add another JOIN and AND statement. I think there is something missing something in the setup,
or I'm just not familiar enough with MySQL to format the query correctly.

Little help?

At first glance, there are some alarm bells ringing.

• You have t_users_settings.t_sid IS NOT NULL AND t_users_settings.t_uid IS NOT NULL. The first seems to imply you might have NULL values in your t_users_settings.t_sid column. The second seems to defeat the purpose of doing a left join to t_users_settings in the first place.

• A second concern is that you're over-generalising.

Notice that you're unable to choose a specific type for your t_users_settings.value column. This means you have to roll your own custom type checking or do without.

Yes your design allows you to add new settings "without code changes". But in reality, those settings would be meaningless without code changes to make use of them in the first place.

You could probably solve your current problems quite easily by defining your settings table with a separate column for each possible setting, and doing the minimal work of adding columns as you add new settings.

However, that said here's a suggestion based on your current design.

First you want to get all permutations of settings and users. For that you need a cross join.

SELECT  u.*, s.*
FROM    t_users u
        CROSS JOIN t_settings s

That gives all combinations with their defaults. Now you want a left outer join to find out which defaults have been overridden for each user.

SELECT  u.*, s.*
FROM    t_users u
        CROSS JOIN t_settings s
        LEFT JOIN t_users_settings us ON
            us.uid = u.uid
        AND us.t_sid = s.t_sid

Finally, cleanup it up with explicit column selection, and you can even derive the actual users_value with a COALESCE, ISNULL or CASE statement.

SELECT  u.uid,
        u.name,
        u.email,
        s.t_sid,
        s.name,
        s.default_value,
        COALESCE(us.value, s.default_value) AS users_value
FROM    t_users u
        CROSS JOIN t_settings s
        LEFT JOIN t_users_settings us ON
            us.uid = u.uid
        AND us.t_sid = s.t_sid

StackExchange Code Review Q#13404, answer score: 5