How to join table with itself, with a second "ON" criteria, if a row is null

Question

I have a User table. Among others, it has these 4 columns:

------ UserID ------- | ---- Username ----- | --- CreatedBy --- | ParentUserID

(PK, bigint, not null) | (char(20), not null) | (varchar(50), null) | (bigint, null)

Both ParentUserID and CreatedBy points at the "owner account", by the UserID or Username respectively. Both are unique.

CreatedBy is never actually null, but UserID is indexed, so ParentUserID is preferred - and it's also the one we're moving towards.

Obviously I'm not fluent in SQL, but this is my idea of it:

SELECT Users.*
  FROM tblUsers AS Owners
    LEFT JOIN tblUsers AS Users
      ON
        ISNULL(Users.ParentUserID = Owners.UserID,
          Users.CreatedBy = Owners.Username)
    WHERE Owners.UserID = 14;

This is as far as I've gotten:

SELECT ISNULL(POwners.UserID, COwners.UserID) AS OwnerID, Users.*
  FROM tblUsers AS Users
    RIGHT JOIN tblUsers AS POwners ON Users.ParentUserID = POwners.UserID
    RIGHT JOIN tblUsers AS COwners ON Users.CreatedBy = COwners.Username
WHERE OwnerID = 14;

Although obviously this doesn't work. On a secondary note, I further need to convert this to LINQ, but for this question, it is only relevant so far that the query will be possible to convert, which I would expect of the vast majority of queries.

score 0 · Answer 1 · answered Sep 23 '16 at 16:40

0

join conditions are just boolean tests, so you need to write a proper boolean condition, e.g. (P or Q) AND R. You can't chain them with ,, so...

... ON ISNULL((Users.ParentUserID = Owners.UserID) AND (Users.CreatedBy = Owners.Username))

or whatever you need the logic to be. Making it a VALID boolean expression is the critical part.

answered Sep 23 '16 at 16:40

Marc B

356,200
43
426
500

I was using the [`ISNULL(a, b)`](https://msdn.microsoft.com/en-us/library/ms184325.aspx) because I was under the impression that it uses the second value if the first is `null`. Similar to the null-coalescing operator in C#: `var user = firstUser ?? secondUser;` By the way you've expressed it, It should read something like `Users.ParentUserID = Owners.UserID OR Users.CreatedBy = Owners.Username` – Aske B. Sep 23 '16 at 16:50
1

can't you just have `on (a=b) or (c=d)`? – Marc B Sep 23 '16 at 16:58
Yeah, that's a valid expression - and returns the right number of rows. But I don't know if it's right, nor do I know how to verify it. Does it return the row if the first condition is true, without checking the second condition? – Aske B. Sep 23 '16 at 17:00
If you want to verify it you can always compare the results with the query I posted ;-) – Thomas G Sep 23 '16 at 17:03

score 0 · Accepted Answer · edited May 23 '17 at 12:19

This seems to be the query that did the trick for me:

SELECT Users.*
  FROM tblUsers AS Owners
    LEFT JOIN tblUsers AS Users
      ON Users.ParentUserID = Owners.ParentUserID
        OR Users.CreatedBy = Owners.Username
  WHERE Owners.UserID = 14;

Thanks to Marc B for the help.

As for LINQ, it turned out only equijoins are supported, so I did a cross join like this:

from u in dbContext.tblUsers
from o in dbContext.tblUsers
where (u.ParentUserID == o.UserID || u.CreatedBy == o.Username)
    && o.UserID == 14
select u;

Which turns into the following query:

SELECT Users.*
    FROM  tblUsers AS Users
        CROSS JOIN tblUsers AS Owners
    WHERE(Users.ParentUserID = Owners.UserID OR Users.CreatedBy = Owners.Username)
        AND(Owners.UserID = 14)

How to join table with itself, with a second "ON" criteria, if a row is null

2 Answers2