Union of data that cannot be joined

Goal: write a query that can bring back all the users of a company, and the company's primary user.

I have funky situation where there is login structure that my company has set in place. The tables look like so:

User

  int        int          bit
UserId | CompanyId | IsPrimaryUser |

UserLoginBridge

  int      int
UserId | LoginId

Login

 int
LoginId | CompanyId | ...

Obviously the relationships here are much less than optimal. But this is what I have to work with. My issue is that users can be assigned to many Companies .. so there is a 1:M relationship bet开发者_Python百科ween User and Login that is expressed by UserLoginBridge.

There is also a Primary User ... indicated by the present of a 1 in the IsPrimaryUser col. In addition, the primary user is never placed into the UserLoginBridge but he has a Login entry .... So the catch is .. I cannot join my way to a primary user.

Any thoughts?

Ideal DataSet

UserId | IsPrimaryUser | CompanyId | LoginId

I'll try to explain the method to my madness.

The subquery "q" in the first SELECT finds minimum login ID for each company. Those should be the logins for the primary user, one per company. I can then join primary users to that subquery on CompanyId.

The second select for regular users should need no explanation.

This could be written without the UNION using COALESCE for CompanyId and LoginId, but I think this version is a little easier to read and understand.

/* Primary User */
SELECT u.UserId, u.IsPrimaryUser, q.CompanyId, q.LoginId
    FROM User u
        INNER JOIN (SELECT l.CompanyId, MIN(l.LoginId)
                        FROM login l
                        GROUP BY l.CompanyId) q
            ON u.CompanyId = q.CompanyId
    WHERE u.IsPrimaryUser = 1
UNION ALL
/* Regular Users */
SELECT u.UserId, u.IsPrimaryUser, l.CompanyId, l.LoginId
    FROM User u
        INNER JOIN UserLoginBridge ulb
            ON u.UserId = ulb.UserId
        INNER JOIN Login l
            ON ulb.LoginId = l.LoginId
    WHERE u.IsPrimaryUser = 0