Home / Questions / 97366
Accepted
JuankGlezz
Asked: 2020-08-26 06:49:40 +0800 CST 2020-08-26 06:49:40 +0800 CST

Get all records that are not associated with an id

  • 772

Related How to group a record that is repeated, showing all the different records that are associated with it?

I'm doing a query to get all the clientesand total de cuentas asociadasbut I don't want to show the ones that I already have associated.

I have the following tables:

----dbClientes-----    --------syUsers--------    -----syUsers_WebAccess----
|  id  |  Nombre  |    |  id  |  Nombre      |    | id | idUser | idClient |
-------------------    -----------------------    --------------------------
|  1   |  Juan    |    |  1   | Contabilidad |    | 1  |    1   |    1     |
|  2   |  Victor  |    |  2   | JuankGlezz   |    | 2  |    1   |    3     |
|  3   |  Arturo  |    -----------------------    | 3  |    2   |    1     |
-------------------                               --------------------------

Doing the following query brings me all the clienteswith the total de cuentas asociadas.

SELECT DISTINCT C.Nombre, C.id, IFNULL(NC.NumCuentas,0) AS NumCuentas
FROM dbclientes C
LEFT JOIN (
    SELECT idClient, count(id) AS NumCuentas FROM syUsers_WebAccess
  ) NC ON NC.idCliente = C.id

It gives me as a result:

Nombre   idCliente  NumCuentas
Juan        1          2
Victor      2          1
Arturo      3          0

Now I want to apply Get all clientesExcept those clientesassociated with it JuankGlezz, that is, I expect this as a result:

Nombre   idCliente  NumCuentas
Victor      2          1
Arturo      3          0

In case the selected user Contabilidadwas the following would be displayed:

Nombre   idCliente  NumCuentas
Victor      2          1

How would the query be to obtain those results? , try this, but it only works if there is only one associated account.

SELECT * FROM (
    SELECT DISTINCT C.Nombre, C.id, IFNULL(NC.NumCuentas,0) AS NumCuentas
    FROM dbclientes C
    LEFT JOIN (
        SELECT idCliente, count(id) AS NumCuentas FROM syUsers_WebAccess
      ) NC ON NC.idCliente = C.id
) TB
WHERE id != (SELECT idCliente FROM syUsers_WebAccess WHERE idUser = 48)

Note: I can't just put the idClient, because from the application I get only theidUser

mysql

2 Answers

  1. Best Answer

    The simplest for this is to use NOT EXISTS:

    SELECT DISTINCT C.Nombre, C.id, IFNULL(NC.NumCuentas,0) AS NumCuentas
FROM dbclientes C
LEFT JOIN ( SELECT  idClient, 
                    count(id) AS NumCuentas 
            FROM syUsers_WebAccess
            GROUP BY idClient) NC 
    ON NC.idCliente = C.id
WHERE NOT EXISTS(SELECT 1 FROM syUsers_WebAccess
                 WHERE idUser = 2 --JuanKGlezz
                 AND idCliente = NC.idCliente)
;

    Here is a demo of this.

    • 6

  2. Another solution is to do a LEFT JOINto table sysUsers_WebAccesand exclude all clienteswhere theidUser = 2

    For example:

    SELECT C.Nombre, C.id, SUM(IF(ISNULL(NC.id), 0, 1)) AS NumCuentas
FROM dbClientes C
  LEFT JOIN syUsers_WebAccess AS NC
    ON NC.idCliente = C.id
WHERE C.id NOT IN (
  SELECT idCliente 
  FROM syUsers_WebAccess 
  WHERE idUser = 2)
GROUP BY C.id

    Demo

    PS : You could replace the one NOT INwith the one in @Lamak'sNOT EXISTS answer

    • 1