CODEWITHSUNDEEP
sqljoin
dardy
dardy

Reputation: 433

SQL request: Join the same table

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

Answers (2)

Javaluca
Javaluca

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

Madhivanan
Madhivanan

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

Related Questions