CODEWITHSUNDEEP
mysql
user14397867
user14397867

Reputation: 37

How to get records that don't exist based on 2 different tables

I have the following table setups:

//clients

+-----------+----------+------------+
| Id        | Client   | Status     |
+-----------+----------+------------+
| 1         | A        | Active     |
| 2         | B        | Active     |
| 3         | C        | Inactive   |
+-----------+----------+------------+

//manager Table

+-----------+----------+
| clientId  | managerId|
+-----------+----------+
| 1         | 1        |
| 2         | 2        |
| 3         | 3        |
+-----------+----------+

//users table

+-----------+----------+
| id        | firstName|
+-----------+----------+
| 1         | Carrera  |
| 2         | Taylor   |
| 3         | Diaz     |
+-----------+----------+

//Desired Output

+-----------+----------+
| clientId  | managerId|
+-----------+----------+
| 1         | 2        |
| 1         | 3        |
| 2         | 1        |
| 2         | 3        |
+-----------+----------+

How can I write a query to get the desired output? I have tried something like this but the output is not desired:

select c.id as "Client"
     , u.id as "user" 
  from managers cm 
  left 
  join users u 
    on u.id = cm.managerId 
  left 
  join clients c 
    on c.id = cm.clientId 
 where cm.managerId not in (select id from users where firstName like '%a%') 
   and cm.clientId not in (select id from clients where status = 'Active');

Upvotes: 1

Views: 42

Answers (1)

nbk
nbk

Reputation: 49410

Th interesting think is the WHEREclause, as you need the active client and exclude the existing connection in the table managers

CREATE TABLE managers (
  `clientId` INTEGER,
  `managerId` INTEGER
);

INSERT INTO managers
  (`clientId`, `managerId`)
VALUES
  ('1', '1'),
  ('2', '2'),
  ('3', '3');

CREATE TABLE Clients (
  `Id` INTEGER,
  `Client` VARCHAR(1),
  `Status` VARCHAR(8)
);

INSERT INTO Clients
  (`Id`, `Client`, `Status`)
VALUES
  ('1', 'A', 'Active'),
  ('2', 'B', 'Active'),
  ('3', 'C', 'Inactive');

CREATE TABLE users (
  `id` INTEGER,
  `firstName` VARCHAR(7)
);

INSERT INTO users
  (`id`, `firstName`)
VALUES
  ('1', 'Carrera'),
  ('2', 'Taylor'),
  ('3', 'Diaz');

SELECT c.`Id` as ClientId ,u.id as ManagerId  FROM users u CROSS JOIN Clients c
WHERE c.`Status` = 'Active' AND (u.id,c.`Id`) NOT IN (SELECT `clientId`, `managerId` FROM managers)
ORDER By ClientId,ManagerId

ClientId | ManagerId
-------: | --------:
       1 |         2
       1 |         3
       2 |         1
       2 |         3

db<>fiddle here

Upvotes: 1

Related Questions