Avoid duplicates without distinct-CodePudding

Is there a way to make the query to return only one record without using distinct ?

CREATE TABLE users (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    registration_number INT(5) UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE tickets (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(150) NOT NULL,
    description TEXT,
    PRIMARY KEY(id)
);

CREATE TABLE tickets_requesters (
    tickets_id INT(11) UNSIGNED NOT NULL,
    users_id INT(11) UNSIGNED NOT NULL
);

CREATE TABLE tickets_recipients (
    tickets_id INT(11) UNSIGNED NOT NULL,
    users_id INT(11) UNSIGNED NOT NULL
);

INSERT INTO users 
  (name, registration_number)
VALUES
  ('Paul', 12),
  ('John', 34);
  
  
INSERT INTO tickets
  (title)
VALUES
  ('Ticket #1'),
  ('Ticket #2');
  
  
INSERT INTO tickets_requesters
  (tickets_id, users_id)
VALUES
  (1, 1),
  (1, 2);
  
INSERT INTO tickets_recipients
  (tickets_id, users_id)
VALUES
  (1, 1);

SELECT 
t.id,
t.title
FROM tickets t
LEFT JOIN tickets_requesters trq ON trq.tickets_id = t.id
LEFT JOIN tickets_recipients trc ON trc.tickets_id = t.id
LEFT JOIN users urq ON urq.id = trq.users_id
LEFT JOIN users urc ON urc.id = trc.users_id
WHERE (urq.registration_number = 12 OR urc.registration_number = 12);

id | title    
-: | :--------
 1 | Ticket #1
 1 | Ticket #1

db<>fiddle here

EDIT :

Thanks to all for your answers. So far there are 3 ways to do it :

With group by
With union
With exists

So, is there a specific use case for each of these methods or can I use either one ?

CodePudding user response：

You could use EXISTS instead of the joins:

SELECT t.id, t.title
FROM tickets t
WHERE EXISTS (
        SELECT 1 
        FROM tickets_requesters trq INNER JOIN users urq 
        ON urq.id = trq.users_id
        WHERE trq.tickets_id = t.id AND urq.registration_number = 12
      )
   OR EXISTS (
        SELECT 1 
        FROM tickets_requesters trc INNER JOIN users urc 
        ON urc.id = trc.users_id
        WHERE trc.tickets_id = t.id AND urc.registration_number = 12
      );

CodePudding user response：

You can use UNION to combine the two kinds of condition you're looking for.

SELECT t.id, t.title
FROM tickets t
JOIN tickets_requesters trq ON trq.tickets_id = t.id
JOIN users urq ON urq.id = trq.users_id
WHERE urq.registration_number = 12 
UNION
SELECT t.id, t.title
FROM tickets t
JOIN tickets_recipients trc ON trc.tickets_id = t.id
JOIN users urc ON urc.id = trc.users_id
WHERE urc.registration_number = 12;

fiddle