find same record for multiple fields in mysql

Question

CREATE TABLE IF NOT EXISTS `userinfo` (
  `user_id` int(6) unsigned NOT NULL,
  `user_code` varchar(20),
  `party1_id` varchar(200),
  `party2_id` varchar(200),
  PRIMARY KEY (`user_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `userinfo` (`user_id`, `user_code`, `party1_id`, `party2_id`) VALUES
  ('1', '05B29E57', '1', ''),
  ('2', '05B29E58', NULL, '1'),
  ('3', '05B29E59', '2', ''),
  ('4', '05B29E60', NULL, '2'),
  ('5', '05B29E61', '3',''),
  ('6', '05B29E62', NULL, '3'),
  ('7', '05B29E63', NULL, '4'),
  ('8', '05B29E64', NULL, '5'),
  ('9', '05B29E65', NULL, '6'),
  ('10', '05B29E66', NULL, '7');

+---------+-----------+-----------+-----------+
| user_id | user_code | party1_id | party2_id |
+---------+-----------+-----------+-----------+
|       1 | 05B29E57  | 1         |           |
|       2 | 05B29E58  | NULL      |         1 |
|       3 | 05B29E59  | 2         |           |
|       4 | 05B29E60  | NULL      |         2 |
|       5 | 05B29E61  | 3         |           |
|       6 | 05B29E62  | NULL      |         3 |
|       7 | 05B29E63  | NULL      |         4 |
|       8 | 05B29E64  | NULL      |         5 |
|       9 | 05B29E65  | NULL      |         6 |
|      10 | 05B29E66  | NULL      |         7 |
+---------+-----------+-----------+-----------+

So i have above table as userinfo. Due to some reason i have to find the duplicate records for party1_id & party2_id.

I have tried below:

SELECT
  *
FROM userinfo u1
  JOIN userinfo u2
    ON u1.party1_id = u2.party2_id
WHERE IFNULL(u1.party1_id, 0) > 0
AND IFNULL(u1.party2_id, '') = ''

Fiddle: http://sqlfiddle.com/#!9/725eff/1

But it didn't give result in a way i want, it just returns 3 record. Is there a way to archive my expected output with sorting same as in my expected output.

Expected Output:

+---------+-----------+-----------+-----------+
| user_id | user_code | party1_id | party2_id |
+---------+-----------+-----------+-----------+
|       1 | 05B29E57  | 1         |           |
|       2 | 05B29E58  | NULL      |         1 |
|       3 | 05B29E59  | 2         |           |
|       4 | 05B29E60  | NULL      |         2 |
|       5 | 05B29E61  | 3         |           |
|       6 | 05B29E62  | NULL      |         3 |
+---------+-----------+-----------+-----------+

In your expected output you have 6 records .. explian better .. — ScaisEdge
– ScaisEdge, Commented Jul 11, 2020 at 6:09
yes that is my expected output but the query which i execute only return 3 record. — DS9
– DS9, Commented Jul 11, 2020 at 6:11
@scaisEdge i am trying to achieve result similar to my expected output (i.e. 6 record) but my actual output has only 3 record. — DS9
– DS9, Commented Jul 11, 2020 at 6:15

manthan · Accepted Answer · 2020-07-11 07:47:50Z

1

You can give a try following.

select * from userinfo where 
party1_id  in (
  SELECT
  u1.party1_id
FROM userinfo u1
  JOIN userinfo u2
    ON u1.party1_id = u2.party2_id
WHERE IsNULL(u1.party1_id, 0) > 0
AND IsNULL(u1.party2_id, '') = '') or party2_id in ( SELECT
  u1.party1_id
FROM userinfo u1
  JOIN userinfo u2
    ON u1.party1_id = u2.party2_id
WHERE IsNULL(u1.party1_id, 0) > 0
AND IsNULL(u1.party2_id, '') = '')

answered Jul 11, 2020 at 7:47

manthan

1321 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2020-07-11 11:07:15Z

1

You could obtain the expected result using union

select t1.* 
from userinfo t1
inner join  (
    select a.party1_id apid, b.party2_id bpid
     FROM userinfo a
      JOIN userinfo b
        ON a.party1_id = b.party2_id
) t2 on t2.apid = t1.party1_id
UNION 
select t1.* 
from userinfo t1
inner join  (
    select a.party1_id apid, b.party2_id bpid
     FROM userinfo a
      JOIN userinfo b
        ON a.party1_id = b.party2_id
) t2 on t2.apid = t1.party2_id
order by user_id

http://sqlfiddle.com/#!9/725eff/17

for performance be sure you have al least thiese indexes

idx1  table userinfo columns (party1_id, party2_id)

idx2 table userinfo columns (party2_id, party1_id)

edited Jul 11, 2020 at 11:07

answered Jul 11, 2020 at 6:14

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

1 Comment

kewlashu Over a year ago

@DS9 I ran a similar query in a shared hosting mysql table with half a million records and on varchar non-indexed columns, it took just 3 seconds. The point is, query in this answer or my answer should not take this long. Try to look at the database, server settings etc.

kewlashu · Accepted Answer · 2020-07-11 06:33:02Z

0

Try with below query:

SELECT
  *
FROM userinfo u1
  where
  u1.party1_id in (select distinct u2.party2_id from userinfo u2) 
  or 
  u1.party2_id in (select distinct u3.party1_id from userinfo u3)

Below is the output I get in your fiddle for above query:(order by can be applied to any column)

user_id     user_code   party1_id   party2_id
1           05B29E57    1   
2           05B29E58    (null)      1
3           05B29E59    2   
4           05B29E60    (null)      2
5           05B29E61    3   
6           05B29E62    (null)      3

edited Jul 11, 2020 at 6:33

answered Jul 11, 2020 at 6:16

kewlashu

1,10911 silver badges19 bronze badges

Collectives™ on Stack Overflow

find same record for multiple fields in mysql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related