i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
Expand|Select|Wrap|Line Numbers
- id ------------ name
- ======================
- 1 -------------- AAA
- 2 -------------- BBB
- 3 -------------- CCC
- 2 -------------- AAA
- 3 -------------- AAA
- 4 -------------- CCC
- 1 -------------- BBB
in the all the searching name.
i wrote the query like this... here i built the query dynamicaly.. if the search name is
increase...table self join is increase for each name... how to avoid this.. without
self join how to write the query.....
My query :
Query1:
Expand|Select|Wrap|Line Numbers
- select a.*
- from
- sample a
- inner join sample b on a.id=b.id
- where (a.name like 'AAA' and b.name ='BBB');
Expand|Select|Wrap|Line Numbers
- 1 -------------- AAA
- 2 -------------- BBB
- 2 -------------- AAA
- 1 -------------- BBB
remove the duplicate.. the query will be slow...
Query2:
Expand|Select|Wrap|Line Numbers
- select a.*
- from
- sample a
- inner join sample b on a.id=b.id
- where (a.name like 'AA%' and b.name ='BB%');
Expand|Select|Wrap|Line Numbers
- 1 -------------- AAA
- 2 -------------- BBB
- 2 -------------- AAA
- 1 -------------- BBB
remove the duplicate.. the query will be slow...
But need to fine tune these query...
Is there any other simple way to get the result...?
How to avoid the self join to get the result...
Can u any one help me...?
Thanks & Regards,
S.Ashokkumar.