[thelist] Joins across UNION statements

[Bill Moseley]

Here's a rather basic SQL question.

I'm using Postgresql.

I have a system that consists of "accounts" and each account has many
"users."  Both a user and an account have one or more settings, and I want to
create a VIEW where I can say:

    SELECT * FROM my_view WHERE user = 1234;

and return the UNION of the two tables for that user (i.e. return a
distinct set of settings that apply to the user from both settings
tables).

Poking around the Internet you can find recommendations against using
UNIONS for performance reasons and suggestions to use a join, instead.
I'm missing how to accomplish that, though.  My attempts keep
returning a Cartesian product.


So, my account and user tables:


    create table account (
        id      int primary key,
        name    text
    );

    create table user (
        id      int primary key,
        name    text,
        account int not null references account
    );

I have some settings that are set both per user AND per account.  So
two more tables:

    create table account_setting (
        id      int primary key,
        setting int,  -- whatever
        account int not null references account
    );


    create table user_setting (
        id      int primary key,
        setting int,  -- whatever
        user int not null references user
    );

When I need settings I know the user id, but I want to grab both the
user's settings and the associated account settings together.

I can easily fetch the settings in two selects:

    SELECT setting FROM user_setting WHERE user = 1234;

and

    SELECT
        setting
    FROM
        account_setting
        JOIN account ON account.id = account_setting.account
        JOIN user ON user.account = account.id
    WHERE
        user.id = 1234;


But, I'm being thick today and not seeing how to accomplish that with
a join instead.


Again, I want to create a view to abstract out how the settings are
fetched.  Some day there might be "settings" that come from, say, a
third table that I want to combine as well but would not want to
change how the application fetches them (instead I'd just update the
view).

Thanks,


-- 
Bill Moseley
moseley at hank.org
Sent from my iMutt