Trích xuất tuple có giá trị chung trong cột khác (self-join) trong MySQL

Tóm tắt

Dùng self-join (join bảng với chính nó) để tìm các cặp hàng có cùng giá trị ở cột khóa nhưng khác giá trị ở cột khác.

Cú pháp

SELECT DISTINCT t1.cot_khoa, t1.cot_gia_tri, t2.cot_khoa, t2.cot_gia_tri
FROM bang t1
INNER JOIN bang t2 ON t1.cot_khoa = t2.cot_khoa
WHERE t1.cot_gia_tri = 'gia_tri_1'
  AND t2.cot_gia_tri = 'gia_tri_2';

Ví dụ

1. Tạo bảng

CREATE TABLE extractTuples (
    Id INT,
    Name VARCHAR(20),
    Comments TEXT
);

2. Dữ liệu

INSERT INTO extractTuples VALUES
(1, 'John', 'hi'),
(2, 'Carol', 'hello'),
(3, 'John', 'Amazing'),
(1, 'Carol', 'Good');

3. Truy vấn: Tìm Id có cả John và Carol

SELECT DISTINCT t1.Id, t1.Name, t2.Id, t2.Name
FROM extractTuples t1
INNER JOIN extractTuples t2 ON t1.Id = t2.Id
WHERE t1.Name = 'John' AND t2.Name = 'Carol';

4. Kết quả

+------+------+------+-------+
| Id   | Name | Id   | Name  |
+------+------+------+-------+
| 1    | John | 1    | Carol |
+------+------+------+-------+

Giải thích

• Self-join trên Id tạo cặp hàng cùng Id.
• WHERE lọc cặp: một hàng Name='John', hàng kia Name='Carol'.
• DISTINCT loại bỏ trùng lặp nếu nhiều hàng cùng tên.

Mở rộng

• Tìm Id có ít nhất 2 tên khác nhau: GROUP BY Id HAVING COUNT(DISTINCT Name) >= 2.
• Lấy tất cả cặp tên cho mỗi Id: bỏ điều kiện WHERE, select t1.Name, t2.Name.