CODEWITHSUNDEEP
mysql
kkh
kkh

Reputation: 4869

mysql find duplicate values across columns in different tables

Given 3 tables A, B and C, each of the tables have a id column which must be unique across the three tables. How do i do a query to do that? right now I am using left join and find the count, but it will not work as this particular id may exists in only one of the three tables

SELECT COUNT(*) as unique_id FROM A
        LEFT JOIN B on B.id= A.id
        LEFT JOIN Con B.id= C.id
 WHERE A.id='{$id}'
    OR B.id='{$id}'
    OR C.id='{$id}'

i am trying to find out if this $id is unique among the three tables.

Upvotes: 0

Views: 449

Answers (2)

peterm
peterm

Reputation: 92785

You can do following

SELECT 
(
   (SELECT COUNT(*) FROM A WHERE id = 3)
 + (SELECT COUNT(*) FROM B WHERE id = 3)
 + (SELECT COUNT(*) FROM C WHERE id = 3)
) unique_count

Here is SQLFiddle demo

Now instead of finding duplicates and assigning ids manually you might want to consider to make them unique across your tables by utilizing a separate table for sequencing with auto_increment and simple triggers.

Your db schema might look like

CREATE TABLE id_seq (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE A (id INT NOT NULL);
CREATE TABLE B (id INT NOT NULL);
CREATE TABLE C (id INT NOT NULL);

Triggers

DELIMITER $$
CREATE TRIGGER tg_a_insert
BEFORE INSERT ON a
FOR EACH ROW
BEGIN
  INSERT INTO id_seq VALUES (NULL);
  SET NEW.id = LAST_INSERT_ID();
END$$
DELIMITER ;

They will be the same for all tables. Just change the name of a trigger and table

Now you can INSERT to any of you tables like

INSERT INTO A (id) VALUES (0);
INSERT INTO B (id) VALUES (0);
INSERT INTO C (id) VALUES (0);

And you will get 

Table A
| ID |
------
|  1 |

Table B
| ID |
------
|  2 |

Table C
| ID |
------
|  3 |

Here is SQLFiddle demo

Upvotes: 1

Barmar
Barmar

Reputation: 780724

Since MySQL doesn't have FULL OUTER JOIN, you have to use a UNION.

SELECT COUNT(*) as unique_id
FROM (SELECT id FROM A WHERE id = '$id'
      UNION ALL
      SELECT id FROM B WHERE id = '$id'
      UNION ALL
      SELECT id FROM C WHERE id = '$id') x

Upvotes: 1

Related Questions