Apologies, I have missed out one condition earlier (Added in Italic and bold)
I have a table with a few thousand rows, and I would like to extract texts from certain columns if there are matching values in different columns.
For example:
| A | B | C | D | E |
|---|---|---|---|---|
| teeth | MrA | 123 | ABC | $128 |
| nose | MrB | CDE | 789 | $100 |
| hand | MrC | ABC | 456 | $128 |
| eye | MrD | EFG | 789 | $110 |
The expected outcome is if any value of C = D or vice versa, and with same E value, then the query should return values from column A & column B or the whole row.
Results would be:
| A | B | C | D | E |
|---|---|---|---|---|
| teeth | MrA | 123 | ABC | $128 |
| hand | MrC | ABC | 456 | $128 |
as C match with D on ABC and E = $128 which is same for both rows.
The query that I have tried thus far can only return the value if C & D have same value in same row. Appreciate help on this, thanks!
CodePudding user response：
Probably the simplest way is to use exists
select *
from t
where exists (select * from t t2 where t.c=t2.d or t.d=t2.c)
CodePudding user response：
Other than Stu's answer, you can also use Cross join but you have to watch out for query performance.
select t2.* --or t.*
from Trt t
cross join Trt t2
where t.C = t2.D or t.D = t2.C;