Reputation: 433
Suppose I have this table "tab"
:
-------------------
id | value | name |
-------------------
1 | 12 | 'to' |
-------------------
2 | 13 | 'to' |
-------------------
3 | 14 | 'gh' |
-------------------
I want to get the lines that have the same names but diffrent values. In this case, it would be the first and second line. I did this request:
select *
from tab t1
join tab t2
on t1.ID = t2.ID
where t1.VALUE != t2.VALUE
and t1.NAME = t2.NAME
But it's taking way too long to give me the results.
So I was wondering if there was another quicker solution?
Thanks!
Upvotes: 0
Views: 84
Reputation: 857
You can try this solution if you don't need join
SELECT *
FROM tab t1
WHERE EXISTS ( SELECT 'x'
FROM tab t2
WHERE t2.id <> t1.id
AND t2.name = t1.name
AND t2.value <> t1.value
)
Upvotes: 1
Reputation: 13700
Try this
select t1.* from your_table as t1 inner join
(
select name from your_table
group by name
having count(distinct value)>1
) as t2 on t1.name=t2.name
Upvotes: 0