CODEWITHSUNDEEP
mysqlsqlsql-servert-sql
Dennis Liu
Dennis Liu

Reputation: 2338

SQL query to find where ID does not exist in another column

I have a table: Accounts

+-----------+-----------------+------+
| AccountNo | ParentAccountNo | name |
+-----------+-----------------+------+
|         1 | null            | ABC  |
|         2 | 1               | ABCD |
|         3 | 1               | CDE  |
|         4 | 2               | DEF  |
|         5 | null            | GHI  |
|         6 | 3               | MNO  |
|         7 | 5               | JKL  |
+-----------+-----------------+------+

I need to get results where AccountNo does not exist in ParentAccountNo.

For example, based on the data above, the results I expect are:

+-----------+-----------------+------+
| AccountNo | ParentAccountNo | name |
+-----------+-----------------+------+
|         4 |               2 | DEF  |
|         6 |               3 | MNO  |
|         7 |               5 | JKL  |
+-----------+-----------------+------+

Will accept answer for MySQL or SQL-Server.

Upvotes: 1

Views: 3623

Answers (1)

Brien Foss
Brien Foss

Reputation: 3367

By joining the table to itself with a LEFT JOIN on the AccountNo = ParentAccountNo, you can then filter out the rows where there were no matching ID's.

WITH Accounts AS(
    SELECT *
    FROM (VALUES (1,null,'ABC'),
                 (2,1,'ABCD'),
                 (3,1,'CDE'),
                 (4,2,'DEF'),
                 (5,null,'GHI'),
                 (6,3,'MNO'),
                 (7,5,'JKL')) V(AccountNo,ParentAccountNo,name))


SELECT t1.* 
FROM Accounts t1
LEFT JOIN Accounts t2 ON t1.AccountNo = t2.ParentAccountNo
WHERE t2.AccountNo IS NULL

enter image description here

[DEMO HERE]

You were curious about the usage of WITH. . .

Here is an equivalent to my shorthand usage of WITH:

CREATE TABLE Accounts (
    AccountNo INT NOT NULL,
    ParentAccountNo INT NULL,
    Name VARCHAR(200))

INSERT INTO Accounts
    SELECT *
    FROM (VALUES (1,null,'ABC'),
                 (2,1,'ABCD'),
                 (3,1,'CDE'),
                 (4,2,'DEF'),
                 (5,null,'GHI'),
                 (6,3,'MNO'),
                 (7,5,'JKL')) V(AccountNo,ParentAccountNo,name)

Upvotes: 7

Related Questions