How to select records that have no relationship between 3 tables

Asked

Viewed 21 times

1

Assuming I have 3 tables: Company, Users and Users (which relates the other two tables)

Enterprise

+----+----------+--+
| ID |   NOME   |  |
+----+----------+--+
|  1 | Empresa1 |  |
|  2 | Empresa2 |  |
|  3 | Empresa3 |  |
|  4 | Empresa4 |  |
+----+----------+--+

Users

+----+--------+--+
| ID |  NOME  |  |
+----+--------+--+
|  1 | Joao   |  |
|  2 | Pedro  |  |
|  3 | Maria  |  |
|  4 | Fátima |  |
+----+--------+--+

`

Usuariosempresa

+----+------------+------------+--+
| ID | usuario_id | empresa_id |  |
+----+------------+------------+--+
|  1 |          1 |          1 |  |
|  2 |          1 |          2 |  |
|  3 |          2 |          1 |  |
|  4 |          2 |          3 |  |
|  5 |          3 |          1 |  |
|  6 |          3 |          2 |  |
|  7 |          3 |          3 |  |
+----+------------+------------+--+

I would like to select all records from the Usuariosempresa table where there is no relationship between User and Company. In this example it would be something like this:

Upshot

+------+------------+------------+
|  ID  | usuario_id | empresa_id |
+------+------------+------------+
| 1    |          1 |          1 |
| 2    |          1 |          2 |
| null |          1 |          3 |
| null |          1 |          4 |
| 3    |          2 |          1 |
| null |          2 |          2 |
| 4    |          2 |          3 |
| null |          2 |          4 |
| 5    |          3 |          1 |
| 6    |          3 |          2 |
| 7    |          3 |          3 |
| null |          3 |          4 |
| null |          4 |          1 |
| null |          4 |          2 |
| null |          4 |          3 |
| null |          4 |          4 |
+------+------------+------------+

I have put all records to illustrate, but I would like to select only those records that are NOT related ( from the example those that are null )

sql

1 answer

2


Lucas, follow a suggestion using a Cross Join between the tables of companies and users to generate all possible combinations between these tables, and a filter with the operator Exists to obtain in the result only the combinations that do not exist in the table Usuariosempresa:

select
  u.id as usuario_id,
  e.id as empresa_id
from Empresa as e
cross join Usuarios as u
where
  not exists
    (select 1 from UsuariosEmpresa as ue
     where ue.usuario_id = u.id and ue.empresa_id = e.id)

I hope it helps

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.