1

I have a table say table1 with three columns lets name them col1 col2 and col3. I have a suspicion that col2 and col3 have some values that are identical i.e. to say there are some rows where col2 = col3. In case, if for these rows col1 is the same I will have no problem in my application. But how to check for those where col1 = col2 but they don't point to the same col1 value.

I mean SELECT * FROM table1 WHERE col2 = col3 gives me records for col2 = col3 in same rows. what I want for each col2 value search col3 if it is same send those records

Something like this (this is pseudo SQL) but in MYSQL

SELECT DISTINCT col2 FROM table1 foreach col2 same_records = SELECT DISTINCT col1 FROM table1 WHERE col2 = col3 records UNION same_records end foreach SELECT records

Is there is a way to get these results (if they exist)? I believe that I have to use self-join on the table.

  • You mean SELECT * FROM table1 WHERE col2 = col3 AND col1 != col2 ? – Akina Jun 14 '18 at 6:38
  • @Akina Please check, I have edited my question. – Andy Joe Jun 14 '18 at 7:13
  • mmm... incomprehensible... maybe try to show an example of the source data and the result for it? – Akina Jun 14 '18 at 7:49
  • @Akina Please check, I have edited my question again. – Andy Joe Jun 14 '18 at 8:12
  • Сorrect me if I'm wrong. You want 1) Eject all records with col3 field values which not exists in any record in col2 field. 2) Select DISTINCT col1 for the remaining records. – Akina Jun 14 '18 at 8:19
1

You mean something like

SELECT col2, col3 FROM table WHERE col2 = col3 GROUP BY col2, col3 
HAVING COUNT(DISTINCT col1) > 1;

Would show you the col2/col3 combinations where col2 = col3 but col1 differs between those rows.

0

Akina: Сorrect me if I'm wrong. You want 1) Eject all records with col3 field values which not exists in any record in col2 field. 2) Select DISTINCT col1 for the remaining records.

Andy Joe: @Akina col3 values which does exist in col2

Variant 1:

SELECT DISTINCT t1.col1
FROM table1 t1
JOIN table1 t2 ON t1.col3 = t2.col2

Variant 2:

SELECT DISTINCT t1.col1
FROM table1 t1
WHERE EXISTS (SELECT 1
              FROM table1 t2 
              WHERE t1.col3 = t2.col2)
  • Thanks for the effort! But it is taking unbearably long time to run self-join – Andy Joe Jun 14 '18 at 12:41
  • @AndyJoe Try variant 2. – Akina Jun 14 '18 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.