By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Need Query help please...

P: 39
hi...

i have the table like this,

Table Name : sample
Total Records : 500000 (Consider like this)

Sample Records:
----------------

id ------------ name
======================
1 -------------- AAA
2 -------------- BBB
3 -------------- CCC
2 -------------- AAA
3 -------------- AAA
4 -------------- CCC
1 -------------- BBB



i want to search based on the name, i need the id value which is must present
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:
------
select a.*
from
sample a
inner join sample b on a.id=b.id
where (a.name like 'AAA' and b.name ='BBB');

Result:
-------
1 -------------- AAA
2 -------------- BBB
2 -------------- AAA
1 -------------- BBB

This the result set for the above query.. but i need the unique id value.. if i
remove the duplicate.. the query will be slow...


Query2:
-------
select a.*
from
sample a
inner join sample b on a.id=b.id
where (a.name like 'AA%' and b.name ='BB%');


Result:
-------
1 -------------- AAA
2 -------------- BBB
2 -------------- AAA
1 -------------- BBB

This the result set for the above query.. but i need the unique id value.. if i
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.
Jul 3 '07 #1
Share this Question
Share on Google+
1 Reply


P: 39
Question : The IDs Are Not Unique ! Are They Supposed To Be ?

Could You Give A Bit More Information On What It Is Your Searching For.

If SELECT......WHERE (a.name = 'AAA' AND b.name = 'BBB');

Then Why Not

If SELECT......WHERE (a.name = 'AAA' AND b.name ='BBB' AND c.name = 'CCC' );

Note : If You Use LIKE, Then You Must Use Wildcards. Eg LIKE 'AA%'
If No Wildcards, Then I Think LIKE 'AAA' Is The Same As = 'AAA'
Jul 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.