MYSQL: Values of a column where two other columns have same value

Question

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.

Answer 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.

Answer 2

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)