0

I am currently in the need to find entrys matching the same pattern in a connection table.

The Table looks like

id    job_id   data1 ext_id
--    ------   ----- -----
1     15       1     3  
2     15       2     7
3     1        1     5  
4     1        2     4  
5     5        1     3
6     5        2     7

so my basic information is the data of job_id 15

id    job_id   data1 ext_id
--    ------   ----- -----
1     15       1     3  
2     15       2     7

I want to find job_id 5 because the data in ext_id and data1 is the same as in job 15. the data of job_id 1 differs, so I don't want to find that. Any idea on how to do it?

Relicted
  • 1
  • 1
  • What you have tried so far? – KMS Sep 25 '17 at 10:25
  • tried different ways to join the table together.. but to be honest I have just no idea on how to select depending of the data. I was thinking about building the sql-query manually after i retrieved the information about which combinations are for job 15 but there must be a more elegant way i hope :) – Relicted Sep 25 '17 at 10:28
  • Try with job_id=5 in where clause – iamsankalp89 Sep 25 '17 at 10:36

1 Answers1

0

I believe you want this:

select   *
from     your_table
group by data1,
         ext_id
having   count(*) > 1

This post explains it: How to find duplicates in 2 columns not 1

EDIT

I believe this should return all rows that have mathcing data1 and ext_id values

select * from table t1
INNER JOIN table t2 ON t1.data1=t2.data1 and t1.ext_id=t2.ext_id
Einārs
  • 240
  • 2
  • 13