Unify columns from different tables while selecting distinct rows

Question

Tables

User

id	name	email	is_active
1	john	john@albert.com	FALSE
2	mike	mike@ss.com	TRUE
3	monica	monica@dunno.com	TRUE
4	joey	joey@as.com	FALSE
5	ross	ross@boss.com	FALSE

Subscriptions

id	house_id	plan name	status
1	1	A banana a month	inactive
2	2	An apple a month	active
3	3	A pear a month	active

House

id	name
1	John's House
2	Mike's House
3	Monica's House
4	Joey's House
5	Ross's House

House_Contact (legacy table)

id	house_id	is_primary
1	1	TRUE
2	2	FALSE
2	3	TRUE

House_User (new table)

id	house_id	is_owner	user_id
1	2	FALSE	2
2	4	FALSE	4
3	5	FALSE	5

Expected Results

The resulting table should include the following:

• Does the user have a subscription regardless of status? If so, include, if not, disregard.
• Get email & is_active from User table (if they have subscription)
• Get is_primary OR is_owner (if they have a subscription)
• Results should be distinct (no duplicate users)

house_id	email	is_owner	is_active
1	john@albert.com	TRUE	FALSE
2	mike@ss.com	FALSE	TRUE
3	monica@dunno.com	TRUE	TRUE

What I tried

SELECT
    u.email AS "email",
    u.is_active AS "is_active",
    h.id AS "house_id",
    is_owner
FROM
    house c
    INNER JOIN (
        SELECT
            house_id,
            user_id
        FROM
            house_user) hu ON h.id = hu.house_id
    INNER JOIN (
        SELECT
            id,
            email,
            is_active
        FROM
            USER) u ON hu.user_id = u.id
    INNER JOIN (
        SELECT
            id,
            email,
            is_primary
        FROM
            house_contact) hc ON u.email = ch.email
    INNER JOIN (
        SELECT
            house_id,
            is_primary is_owner
        FROM
            house_contact
    UNION
    SELECT
        house_id,
        is_owner is_owner
    FROM
        house_user) t ON u.id = t.house_id)
ORDER BY
    u.email

Results are half than if I remove the INNER JOIN with UNION statement. No idea how to proceed.

I'm particularly confused with unifying the column and the possible duplication.

Answer 1

My educated guess:

SELECT DISTINCT ON (u.id)
      u.id, u.email, u.is_active, h.house_id, h.is_primary
FROM  "user" u
LEFT  JOIN (
   SELECT hu.user_id, hu.house_id
        , GREATEST(hc.is_primary, hu.is_owner) AS is_primary
   FROM   house_user hu
   LEFT   JOIN house_contact hc USING (house_id)
   WHERE  EXISTS (SELECT FROM subscription WHERE house_id = hu.house_id)
   ) h ON h.user_id = u.id
ORDER  BY u.id, h.is_primary DESC NULLS LAST, h.house_id;

We don't need table house in the query at all.

I see three possible sources of conflict:

1. house_contact.is_primary vs. house_user.is_owner. Both seem to mean the same. The DB design is broken in this respect. Taking GREATEST() of both, which means true if either is true.

2. We don't care about subscription.status, so just make sure the house has at least one subscription of any kind with EXISTS, thereby avoiding possible duplicates a priori.

3. A user can live in multiple houses. We want only one row per user. So show the first house with is_primary (the one with the smallest house_id) if any. If there is no house, there is also no subscription. But the outer LEFT JOIN keeps the user in the result. Change to JOIN to skip users without subscription.

About DISTINCT ON:

• Select first row in each GROUP BY group?

About sorting boolean values:

• Sorting null values after all others, except special
• Sort NULL values to the end of a table

Answer 2

You can use the joins as follows:

Select distinct hu.house_id, u.email, hu.is_owner, hc.is_primary
  From user u join house_user hu on u.id = hu.user_id
  Join subscriptions s on s.house_id = hu.house_id
  Join house_contract hc on hc.house_id = s.house_id;

I have used distinct to remove duplicates if you have multiple data in the table for matching condition. You can remove it if not required in case it is not required.

Answer 3

From what I can tell, you want to start with a query like this:

select s.house_id, u.email, hu.is_owner, u.is_active      
from subscriptions s left join
     house_user hu
     on s.house_id = hu.house_id left join
     users u
     on hu.user_id = u.id;

This does not return what you want, but it is rather unclear how your results are derived.