473,320 Members | 1,828 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,320 software developers and data experts.

Need Query.. Help Please...

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
1 1523
debasisdas
8,127 Expert 4TB
If you are usin like for pattern matching there no sense using a.name like 'AAA'

U can use = instead.
Jul 3 '07 #2

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

Similar topics

2
by: lawrence | last post by:
I've been bad about documentation so far but I'm going to try to be better. I've mostly worked alone so I'm the only one, so far, who's suffered from my bad habits. But I'd like other programmers...
5
by: lkrubner | last post by:
I have a webserver through Rackspace. I create a domain. I create an FTP user. I upload some files. I create a database called testOfSetupScript and then I create a database user named setup. I...
4
by: sah | last post by:
I need some help with the following query: DECLARE @SRV VARCHAR(20), @date smalldatetime SET @SRV = (select @@servername) SET @date = '20040901' select Srv_Name = @SRV, DB_Name = 'DB_NAME',...
20
by: andy.rich | last post by:
I am getting the following error and I do not know why. Can anyone help? -------------------------------------------------------- this is what appears on the screen...
5
by: Bob Weisenburger | last post by:
I have a table of "memos". each record in that table has a primary key "memo ID" that is autonumber. I have another table that is "memo receipts" with each record having a "receipt id" field that...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
4
by: access baby | last post by:
i have a huge database based on date and time need to create different report we need to measure our work processes how many order received , order cancelled, completed and count of items completed...
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: id ------------ name
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: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.