473,225 Members | 1,216 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

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 1678
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

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

Similar topics

1
by: AVL | last post by:
Hi I'm working on indexing a website. I want to restict the search to only few file types like .doc,.txt and.ppt How to specify the file types in the 'ixsso.Query' object Presently i'n using...
0
by: Miguel Dias Moura | last post by:
Hello, I am working on an Asp.Net 2.0 / SQL 2005 web site. I am using profile to save the users info on the database. For example, I have the following structure: Public Structure Name...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
0
by: Balamurugan Ranganathan | last post by:
I want to calculate the execution time of a sql query through C#.net this is to analysis two queries to compare their execution time it is very Urgent Please help me Please help Me ...
3
by: Sandy911 | last post by:
Ive never used charts in Access. Somehow I managed to format well shaped charts in Excel but I cant find the way to understand how to create a report in Access that produces a bar or a pie chart...
2
by: mirzaali | last post by:
Hi All, I neeed to dispaly the data in html table tag Query :-select comp,total,funds from table1, WHICH WIL RETURN 3-RECORDSETS rs_query(0) rs_query(1) rs_query(2)
2
by: cj | last post by:
My code below gives me "SQL: Column 'BTN' is not found." It will work if I use an inner join or just join but those two joins don't return all the rows. BTN has 5 rows, but arcust01 has only has...
1
by: veerunrt | last post by:
Hi All, one column is having no of values separated by comma in the table that any one value should match with some other column of another table one value how to join in oracle I need result...
5
by: Justin | last post by:
Here's my XML: <?xml version="1.0" ?> <AppMode Type="Network"> <CurrentFolder Path="c:\tabs"> <Tabs> <FilePath>tabs\Justin.tab</FilePath> <FilePath>tabs\Julie.tab</FilePath> *****There could...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.