Question 192521 in Mysql Question posted in
The official MySQL documentation can be found here.

Is there a better way to express this in MySQL?

Suppose I have clients and services of the following schema:

create table client (
    id INT NOT NULL,
    name varchar(100) NOT NULL,
    PRIMARY KEY ( id )
);

create table service (
    id INT NOT NULL,
    client_id INT NOT NULL,
    status varchar(36) NOT NULL,
    PRIMARY KEY ( id )
);

And the requirement is to select all clients which have all their services deleted (status = ‘DELETED’). What would be the best approach?

My try is:

SELECT DISTINCT service.client_id
FROM service
WHERE service.id NOT IN (SELECT service.id FROM service s WHERE s.status <> 'DELETED' AND s.client_id = service.client_id);

But it only selects client_id, probably need some join.

DB fiddle

Tags:

6

Answers


  1. 0

    you can use exists

    Schema (MySQL v8.0)

    create table client (
    id INT NOT NULL,
    name varchar(100) NOT NULL,
    PRIMARY KEY ( id )
);

create table service (
    id INT NOT NULL,
    client_id INT NOT NULL,
    status varchar(36) NOT NULL,
    PRIMARY KEY ( id )
);

# Client 1
insert into client(id, name) VALUES (1, "client 1");
insert into service(id, client_id, status) VALUES (0, 1, 'DELETED');
insert into service(id, client_id, status) VALUES (1, 1, 'ACTIVE');

# Client 2
insert into client(id, name) VALUES (2, "client 2");
insert into service(id, client_id, status) VALUES (2, 2, 'DELETED');
insert into service(id, client_id, status) VALUES (3, 2, 'DELETED');

# Client 3
insert into client(id, name) VALUES (3, "client 3");
insert into service(id, client_id, status) VALUES (4, 3, 'DELETED');
insert into service(id, client_id, status) VALUES (5, 3, 'ACTIVE');

    Query #1

    SELECT DISTINCT service.client_id
FROM service
WHERE  NOT EXISTS (SELECT 1 FROM service s WHERE s.status <> 'DELETED' AND s.client_id = service.client_id);
    client_id
    2

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    This looks like you need a not exists

    SELECT id, name
FROM client c
WHERE NOT EXISTS (
  SELECT * FROM service s 
  WHERE s.client_id = c.id AND status != 'DELETED'
);
    Login or Signup to reply.
  3. 0

    If you also need the service.id, use a combination of join and exists.

    SELECT c.id AS client_id, c.name, s.id AS service_id
FROM client c
LEFT JOIN service s ON c.id = s.client_id
WHERE NOT EXISTS (
    SELECT 1
    FROM service s2
    WHERE s2.client_id = c.id AND s2.status <> 'DELETED'
);
    Login or Signup to reply.
  4. 0

    You could count existing services and just filter using HAVING

    SELECT service.client_id, SUM(IF(service.status <> 'DELETED', 1, 0)) existing_services
FROM service
GROUP BY service.client_id
HAVING existing_services = 0; 

    | client_id | existing_services |
| --------- | ----------------- |
| 2         | 0                 |
    Login or Signup to reply.
  5. 0

    You can use the group by and having clauses, with the condition that the total number of services equals the total number of services with status DELETED:

    select c.*
from client c
inner join (
  select client_id
  from service
  group by client_id
  having count(case when status = 'DELETED' then 1 end) = count(*)
) as s on s.client_id = c.id

    Result :

    id name
    2 client 2

    Demo here

    Login or Signup to reply.
  6. 0

    You can do it just with a JOIN:

    SELECT  cl.id, cl.name
FROM client cl
left join service sr on sr.client_id=cl.id and sr.status<>'DELETED'
where sr.id is null
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search