468,471 Members | 1,595 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,471 developers. It's quick & easy.

Need Query... Urgent Please help....

hi...

i have the table like this,

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

Sample Records:
Expand|Select|Wrap|Line Numbers
  1. id ------------ name
  2. ======================
  3. 1 -------------- AAA
  4. 2 -------------- BBB
  5. 3 -------------- CCC
  6. 2 -------------- AAA
  7. 3 -------------- AAA
  8. 4 -------------- CCC
  9. 1 -------------- BBB
  10.  
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:
Expand|Select|Wrap|Line Numbers
  1. select a.*
  2. from
  3.      sample a
  4.      inner join sample b on a.id=b.id
  5. where (a.name like 'AAA' and b.name ='BBB');
  6.  
Result:
Expand|Select|Wrap|Line Numbers
  1. 1 -------------- AAA
  2. 2 -------------- BBB
  3. 2 -------------- AAA
  4. 1 -------------- BBB
  5.  
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:
Expand|Select|Wrap|Line Numbers
  1. select a.*
  2. from
  3.      sample a
  4.      inner join sample b on a.id=b.id
  5. where (a.name like 'AA%' and b.name ='BB%');
  6.  
Result:
Expand|Select|Wrap|Line Numbers
  1. 1 -------------- AAA
  2. 2 -------------- BBB
  3. 2 -------------- AAA
  4. 1 -------------- BBB
  5.  
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
1 1462
Atli
5,058 Expert 4TB
Thats weird. I ran you query on a table I created like this:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE test
  2. (
  3.   ID INT NOT NULL,
  4.   Name VARCHAR(255) NOT NULL,
  5.   Primary Key (ID, Name)
  6. )
  7.  
And I get these results:
Expand|Select|Wrap|Line Numbers
  1. ID    Name
  2. --------
  3. 1    AAA
  4. 2    AAA
  5.  
How was your table created?
Jul 3 '07 #2

Post your reply

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

Similar topics

2 posts views Thread by cj | last post: by
5 posts views Thread by Justin | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.