CODEWITHSUNDEEP
sqlsql-servert-sql
userone
userone

Reputation: 321

Find rows that doesn't exist in another table

I have the following tables.

Application Table

Id: 1, 2, 3

User Table

Id, 1, 2, 3

ApplicationUser Table (A bridge table)

AppId   UserId
1       1
1       2
2       1
2       3

I need a SQL query to fill up the ApplicationUser table (i.e. the table should be)

ApplicationUser Table (A bridge table)
AppId   UserId
1       1
1       2
1       3
2       1
2       2
2       3
3       1
3       2
3       3

So the query should return (1, 3) (2, 2) (3, 1) (3, 2) (3, 3)

Upvotes: 0

Views: 70

Answers (3)

AakashM
AakashM

Reputation: 63378

When deciding what records to add, use a CROSS JOIN to get all the records that should and an EXCEPT to remove the ones that already do:

INSERT ApplicationUser (AppId, UserId)
    SELECT a.Id, u.Id
    FROM Application a CROSS JOIN User u
    EXCEPT
    SELECT AppId, UserId FROM ApplicationUser
;

Upvotes: 0

June7
June7

Reputation: 21379

If you are using Access query, this is a Cartesian association of records.

INSERT INTO ApplicationUser(AppID, UserID)
SELECT Application.ID, User.ID
FROM Application, [User];

Set the ID fields in ApplicationUser as compound index with NoDuplicates and duplicate pairs will not be allowed.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Use a cross join with exists logic:

INSERT INTO ApplicationUser (AppId, UserId)
SELECT a.Id, u.Id
FROM Application a
CROSS JOIN User u
WHERE NOT EXISTS (SELECT 1 FROM ApplicationUser au
                  WHERE au.AppId = a.Id AND au.UserId = u.Id);

Upvotes: 1

Related Questions