Expanding a working query without adding multiple join statements?
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