|
Home > Archive > Visual FoxPro SQL Queries > February 2006 > HOW TO GET OPPOSITE OF UNION IN SQL
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
HOW TO GET OPPOSITE OF UNION IN SQL
|
|
| Dinesh 2006-01-20, 7:24 am |
| Hi,
I know that UNION joins the outputs of 2 or more SELECT SQLs leaving out the
common records.
What i want is the common records only. i.e. the opposite of UNION.
how do i achieve that. I tried using NOT UNION to negate the results. but it
gives syntax error.
thanks for the help in advance.
rgds,
dinesh
| |
| Turan Fettahoglu 2006-01-20, 9:24 am |
| > I know that UNION joins the outputs of 2 or more SELECT SQLs leaving out
> the
> common records.
>
> What i want is the common records only. i.e. the opposite of UNION.
select field1, field2, field3 from table1, table2 where table1.key ==
table2.key
Doesn't this work?
Turan
| |
| Dinesh 2006-01-20, 11:24 am |
| Hi Turan,
i want to select records from the same dbf itself.
say for eg. if my file structure was:
mydbf.dbf:
id c(1) (can contain values like 1,1,2,2)
value c(1) (can contain values like 'A','B','C','D')
i want to find all ids having values='A' and 'B'
i tried using:-
select id, value
from mydbf a, mydbf b
where a.id_key = b.id_key and (a.value='A' and b.value='B')
My problem is that I now want to search for all ids having values='A' and
'B' and 'C' and 'D'.
for this will I have to open the same dbf 4 times in the Select statement.
regards,
dj
====================
"Turan Fettahoglu" wrote:
>
> select field1, field2, field3 from table1, table2 where table1.key ==
> table2.key
>
> Doesn't this work?
>
> Turan
>
>
| |
| Olaf Doschke 2006-01-20, 11:24 am |
| > I know that UNION joins the outputs of 2 or more SELECT SQLs leaving out the
> common records.
That's not really true
if you have three selects each selecting one numeric field.
select 1 result: 1,2
select 2 result: 2,3
select 3 result. 3,4
there is no number common to all results here.
Union will have 1,2,3,4 as result. It's simply appending all results,
leaving out duplicates, even if the duplicates are only between
2 of the 3 results.
And with your other problem with A,B,C and D:
Yes, you may do 3 self joins, what's the problem?
Bye, Olaf.
| |
| Cindy Winegarden 2006-01-21, 8:24 pm |
| Hi Dinesh,
Does this do what you want?
Create Cursor Test (ID I, Value C(1))
Insert Into Test Values (1, "A")
Insert Into Test Values (2, "A")
Insert Into Test Values (3, "A")
Insert Into Test Values (4, "A")
Insert Into Test Values (1, "B")
Insert Into Test Values (4, "B")
Insert Into Test Values (1, "C")
Insert Into Test Values (3, "C")
Insert Into Test Values (4, "C")
Insert Into Test Values (1, "D")
Insert Into Test Values (2, "D")
Insert Into Test Values (4, "D")
Select T1.* From Test T1 Where ;
T1.ID In (Select T2.ID From Test T2 Where T2.Value = "A") And ;
T1.ID In (Select T3.ID From Test T3 Where T3.Value = "B") And ;
T1.ID In (Select T4.ID From Test T4 Where T4.Value = "C") And ;
T1.ID In (Select T5.ID From Test T5 Where T5.Value = "D")
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
"Dinesh" <Dinesh@discussions.microsoft.com> wrote in message
news:3332D2F6-9EFD-4A84-B492- 9E78306BAE6F@microso
ft.com...
> Hi,
>
> I know that UNION joins the outputs of 2 or more SELECT SQLs leaving out
> the
> common records.
>
> What i want is the common records only. i.e. the opposite of UNION.
>
> how do i achieve that. I tried using NOT UNION to negate the results. but
> it
> gives syntax error.
>
> thanks for the help in advance.
>
> rgds,
> dinesh
>
| |
| Dinesh 2006-01-23, 7:24 am |
| hi cindy,
many thanks for your example. that's exactly what i wanted.
regards,
dinesh
"Cindy Winegarden" wrote:
> Hi Dinesh,
>
> Does this do what you want?
>
> Create Cursor Test (ID I, Value C(1))
> Insert Into Test Values (1, "A")
> Insert Into Test Values (2, "A")
> Insert Into Test Values (3, "A")
> Insert Into Test Values (4, "A")
> Insert Into Test Values (1, "B")
> Insert Into Test Values (4, "B")
> Insert Into Test Values (1, "C")
> Insert Into Test Values (3, "C")
> Insert Into Test Values (4, "C")
> Insert Into Test Values (1, "D")
> Insert Into Test Values (2, "D")
> Insert Into Test Values (4, "D")
>
> Select T1.* From Test T1 Where ;
> T1.ID In (Select T2.ID From Test T2 Where T2.Value = "A") And ;
> T1.ID In (Select T3.ID From Test T3 Where T3.Value = "B") And ;
> T1.ID In (Select T4.ID From Test T4 Where T4.Value = "C") And ;
> T1.ID In (Select T5.ID From Test T5 Where T5.Value = "D")
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
>
>
> "Dinesh" <Dinesh@discussions.microsoft.com> wrote in message
> news:3332D2F6-9EFD-4A84-B492- 9E78306BAE6F@microso
ft.com...
>
>
>
| |
| Dinesh 2006-01-24, 9:24 am |
| hi cindy,
i tried your sql statement on my dbf which contains over 20 million records.
but it takes a very long time and i had to stop it in between after waiting
for almost over 20 mins.
my dbf which holds over 20 million records has just 2 fields just like the
one you have shown in your example below.
is it that the "IN" command doesnt work if there are many records selected
in the SELECT stmt.
I also tried using COUNT(*) in SQL but no luck.
any help would be appreciated.
regards,
dinesh.
"Cindy Winegarden" wrote:
> Hi Dinesh,
>
> Does this do what you want?
>
> Create Cursor Test (ID I, Value C(1))
> Insert Into Test Values (1, "A")
> Insert Into Test Values (2, "A")
> Insert Into Test Values (3, "A")
> Insert Into Test Values (4, "A")
> Insert Into Test Values (1, "B")
> Insert Into Test Values (4, "B")
> Insert Into Test Values (1, "C")
> Insert Into Test Values (3, "C")
> Insert Into Test Values (4, "C")
> Insert Into Test Values (1, "D")
> Insert Into Test Values (2, "D")
> Insert Into Test Values (4, "D")
>
> Select T1.* From Test T1 Where ;
> T1.ID In (Select T2.ID From Test T2 Where T2.Value = "A") And ;
> T1.ID In (Select T3.ID From Test T3 Where T3.Value = "B") And ;
> T1.ID In (Select T4.ID From Test T4 Where T4.Value = "C") And ;
> T1.ID In (Select T5.ID From Test T5 Where T5.Value = "D")
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
>
>
> "Dinesh" <Dinesh@discussions.microsoft.com> wrote in message
> news:3332D2F6-9EFD-4A84-B492- 9E78306BAE6F@microso
ft.com...
>
>
>
| |
| Leonid 2006-01-24, 11:24 am |
| May be
select ID from TEST group by 1 having count(distinct value)=4
or
select * from TEST where ID in ;
(select ID from TEST group by 1 having count(distinct value)=4)
will be better?
Leonid
"Dinesh" <Dinesh@discussions.microsoft.com> wrote in message
news:1048C26E-D1C2-4FC7-819E- 6A57650D42F8@microso
ft.com...[color=darkred]
> hi cindy,
>
> i tried your sql statement on my dbf which contains over 20 million
> records.
> but it takes a very long time and i had to stop it in between after
> waiting
> for almost over 20 mins.
>
> my dbf which holds over 20 million records has just 2 fields just like the
> one you have shown in your example below.
>
> is it that the "IN" command doesnt work if there are many records selected
> in the SELECT stmt.
>
> I also tried using COUNT(*) in SQL but no luck.
>
> any help would be appreciated.
>
> regards,
> dinesh.
>
>
> "Cindy Winegarden" wrote:
>
| |
| Cindy Winegarden 2006-01-24, 8:25 pm |
| Hi Leonid,
That gives much better results when I use Sys(3054) to check the
optimization, but it depends on there being only four distinct Values (A, B,
C, and D).
Unfortunately, I haven't found a way to better optimize the case that
compares to specific values of Value.
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
"Leonid" <leonid@NOgradaSPAM.lv> wrote in message
news:uieEQCQIGHA.1188@TK2MSFTNGP14.phx.gbl...
> select * from TEST where ID in ;
> (select ID from TEST group by 1 having count(distinct value)=4)
| |
| Leonid 2006-01-25, 3:28 am |
| Hi, Cindy
May be I didn't read question carefully. I understood it as if poster wants
to find ID which corresponds to ALL possible values. If not, this of course
is not a way.
Leonid
"Cindy Winegarden" < cindy_winegarden@msn
.com> wrote in message
news:%230s57RTIGHA.208@tk2msftngp13.phx.gbl...
> Hi Leonid,
>
> That gives much better results when I use Sys(3054) to check the
> optimization, but it depends on there being only four distinct Values (A,
> B, C, and D).
>
> Unfortunately, I haven't found a way to better optimize the case that
> compares to specific values of Value.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
>
>
> "Leonid" <leonid@NOgradaSPAM.lv> wrote in message
> news:uieEQCQIGHA.1188@TK2MSFTNGP14.phx.gbl...
>
>
| |
| Dinesh 2006-01-26, 5:01 pm |
| Hi Leonid/Cindy,
Thanks for your replies. But as Cindy pointed I do not want the result for
all the possible values in the VALUE field.
There might be say A to Z values in the VALUE field but I might want to
query on finding the number of records containing say for eg. just A and B
and C only.
btw, i tried the below sqls and got my result much quicker. though i was
hoping to get the results using just one sql stmt.
SELECT DISTINCT Id, Value FROM mydbf INTO CURSOR c1
SELECT Id, COUNT(*) as Total FROM c1 INTO CURSOR c2 GROUP BY 1
SELECT Id WHERE Total=3 FROM c2 INTO CURSOR c3
anyway better way would still be appreciated.
rgds,
Dinesh
***********
"Leonid" wrote:
> Hi, Cindy
>
> May be I didn't read question carefully. I understood it as if poster wants
> to find ID which corresponds to ALL possible values. If not, this of course
> is not a way.
>
> Leonid
>
>
> "Cindy Winegarden" < cindy_winegarden@msn
.com> wrote in message
> news:%230s57RTIGHA.208@tk2msftngp13.phx.gbl...
>
>
>
| |
| Dinesh 2006-01-26, 5:01 pm |
| sorry the stmts should be:-
SELECT DISTINCT Id, Value ;
FROM mydbf ;
WHERE Value='A' or Value='B' or Value='C' ;
INTO CURSOR c1
SELECT Id, COUNT(*) as Total FROM c1 INTO CURSOR c2 GROUP BY 1
SELECT Id WHERE Total=3 FROM c2 INTO CURSOR c3
rgds,
dinesh
"Leonid" wrote:
> Hi, Cindy
>
> May be I didn't read question carefully. I understood it as if poster wants
> to find ID which corresponds to ALL possible values. If not, this of course
> is not a way.
>
> Leonid
>
>
> "Cindy Winegarden" < cindy_winegarden@msn
.com> wrote in message
> news:%230s57RTIGHA.208@tk2msftngp13.phx.gbl...
>
>
>
| |
| Leonid 2006-01-26, 5:01 pm |
| In one SQL statement it will be
select ID from Mydbf where Value='A' or Value='B' or Value='C' ;
group by 1 having count(distinct Value)=3
But usually spliting one complex SQL statement into several statements may
give better performance
Leonid
"Dinesh" <Dinesh@discussions.microsoft.com> wrote in message
news:1C1AB34E-3ED8-4FFB-B406- 815627BAD797@microso
ft.com...[color=darkred]
> sorry the stmts should be:-
>
> SELECT DISTINCT Id, Value ;
> FROM mydbf ;
> WHERE Value='A' or Value='B' or Value='C' ;
> INTO CURSOR c1
>
> SELECT Id, COUNT(*) as Total FROM c1 INTO CURSOR c2 GROUP BY 1
>
> SELECT Id WHERE Total=3 FROM c2 INTO CURSOR c3
>
> rgds,
> dinesh
>
>
> "Leonid" wrote:
>
| |
| Dinesh 2006-01-26, 5:01 pm |
| Hi,
I could also achieve the results using just 2 sql stmts by making use of
Leonid's example.
SELECT Id, Value ;
FROM mydbf ;
WHERE Value='A' or Value='B' or Value='C' ;
INTO CURSOR c1
SELECT Id from c1 GROUP BY 1 HAVING COUNT(DISTINCT Value)=3
thanks and rgds,
dinesh
"Leonid" wrote:
> Hi, Cindy
>
> May be I didn't read question carefully. I understood it as if poster wants
> to find ID which corresponds to ALL possible values. If not, this of course
> is not a way.
>
> Leonid
>
>
> "Cindy Winegarden" < cindy_winegarden@msn
.com> wrote in message
> news:%230s57RTIGHA.208@tk2msftngp13.phx.gbl...
>
>
>
| |
| Dinesh 2006-01-26, 5:01 pm |
| Hi Leonid,
Many thanks.
Regards,
Dinesh
"Leonid" wrote:
> In one SQL statement it will be
>
> select ID from Mydbf where Value='A' or Value='B' or Value='C' ;
> group by 1 having count(distinct Value)=3
>
> But usually spliting one complex SQL statement into several statements may
> give better performance
>
> Leonid
>
> "Dinesh" <Dinesh@discussions.microsoft.com> wrote in message
> news:1C1AB34E-3ED8-4FFB-B406- 815627BAD797@microso
ft.com...
>
>
>
| |
|
| Hi Dinesh
Leonid's solution can also be written anothere way (if you're using VFP)):
SELECT DISTINCT id FROM test As T1 WHERE VALUE IN ('A','B','C') AND 3 <= ;
(SELECT COUNT(*) FROM test As T2 WHERE T1.id=T2.id)
or
SELECT DISTINCT id FROM test As T1 WHERE VALUE IN ('A','B','C') AND 3 = ;
(SELECT COUNT(*) FROM test As T2 WHERE T1.id=T2.id AND value in
('A','B','C') )
You'll have to compare which one is faster
-Anders
"Dinesh" <Dinesh@discussions.microsoft.com> skrev i meddelandet
news:DDAA929D-596F-4323-B285- 9D25F1D4FBBC@microso
ft.com...[color=darkred]
> Hi,
>
> I could also achieve the results using just 2 sql stmts by making use of
> Leonid's example.
>
> SELECT Id, Value ;
> FROM mydbf ;
> WHERE Value='A' or Value='B' or Value='C' ;
> INTO CURSOR c1
>
> SELECT Id from c1 GROUP BY 1 HAVING COUNT(DISTINCT Value)=3
>
> thanks and rgds,
> dinesh
>
>
> "Leonid" wrote:
>
| |
| Dinesh 2006-01-27, 7:24 am |
| Hi Anders
Thanks for your suggestion. However after trying out all alternatives,
Leonids SQL works much faster.
Just to give you an idea of what my database is like that I am working on:-
My database holds @ 700,000 urns and each urn has a string of different
values in a text field of size C(254) delimited by comma. I now had to change
that field to type memo since it has reached its max limit.
Current database structure:
==================
URN C(8)
Value C(254) ==> eg. A001,A002,A003 etc
All this has made my queries work very slow.
So I have decided to change the structure as follows:
New structure:
==========
ID C(8) (holds the URN from previous structure)
Value C(4)
Now I have multiple records for each URN and runs into almost 23 million
records.
I run various counts on this database all the time and hence need to come up
with SQL stmts which works best for OR values, AND values, or both.
Thanks to all of you for your kind support.
Regards,
Dinesh
******************
"AA" wrote:
> Hi Dinesh
> Leonid's solution can also be written anothere way (if you're using VFP)):
>
> SELECT DISTINCT id FROM test As T1 WHERE VALUE IN ('A','B','C') AND 3 <= ;
> (SELECT COUNT(*) FROM test As T2 WHERE T1.id=T2.id)
> or
> SELECT DISTINCT id FROM test As T1 WHERE VALUE IN ('A','B','C') AND 3 = ;
> (SELECT COUNT(*) FROM test As T2 WHERE T1.id=T2.id AND value in
> ('A','B','C') )
>
> You'll have to compare which one is faster
>
> -Anders
>
> "Dinesh" <Dinesh@discussions.microsoft.com> skrev i meddelandet
> news:DDAA929D-596F-4323-B285- 9D25F1D4FBBC@microso
ft.com...
>
>
>
| |
| jatinder.singh@clovertechnologies.com 2006-01-30, 3:24 am |
| Hi Dinesh,
There is a simple trick we can use to find the Opposite of UNION
(Intersect)
Select A,B,C,D From
(
Select A,B,C,D From Table1
Union All
Select A,B,C,D From Table2
) XYZ Group by A,B,C,D Having count(*) > 1
I hope this help to solve your problem.
With Warm regards
Jatinder Singh
| |
|
| The new structure should be much better for searching even though there are
more rows. Top speed can only be reached on indexed columns. Are both
columns indexed? Do your queries use EXECTLY the same search expression as
the index expression?
-Anders
"Dinesh" <Dinesh@discussions.microsoft.com> skrev i meddelandet
news:61EAE305-B8CB-4A55-AFE5- 61837E91E231@microso
ft.com...[color=darkred]
> Hi Anders
>
> Thanks for your suggestion. However after trying out all alternatives,
> Leonids SQL works much faster.
>
> Just to give you an idea of what my database is like that I am working
> on:-
>
> My database holds @ 700,000 urns and each urn has a string of different
> values in a text field of size C(254) delimited by comma. I now had to
> change
> that field to type memo since it has reached its max limit.
>
> Current database structure:
> ==================
> URN C(8)
> Value C(254) ==> eg. A001,A002,A003 etc
>
> All this has made my queries work very slow.
>
> So I have decided to change the structure as follows:
>
> New structure:
> ==========
> ID C(8) (holds the URN from previous structure)
> Value C(4)
>
> Now I have multiple records for each URN and runs into almost 23 million
> records.
>
> I run various counts on this database all the time and hence need to come
> up
> with SQL stmts which works best for OR values, AND values, or both.
>
> Thanks to all of you for your kind support.
>
> Regards,
> Dinesh
>
> ******************
>
> "AA" wrote:
>
| |
| Dinesh 2006-01-30, 9:28 am |
| Hi Anders,
Yes both fields (ID and Value) are indexed. Also my queries use EXACTLY the
same search expression as the index expression. So my queries are all fully
optimizable.
The only problem I feel in using this new structure is I now can't use the
simple COUNT command to run my queries and have to use SELECT SQL which could
get a bit complex before I get my end result.
for eg. with my earlier structure i could do the below count in one go using
the COUNT command like:
COUNT FOR (('FA01'$Value AND 'FA02'$Value) OR 'FA03'$Value) AND NOT
(('FB01'$Value OR 'FB02'$Value) AND 'FB03'$Value)
However I have to agree that keeping in mind that the database can keep
growing to over a million unique records, I have no choice but to adapt the
new structure for speed purpose.
Regards,
Dinesh
"AA" wrote:
> The new structure should be much better for searching even though there are
> more rows. Top speed can only be reached on indexed columns. Are both
> columns indexed? Do your queries use EXECTLY the same search expression as
> the index expression?
> -Anders
>
>
>
> "Dinesh" <Dinesh@discussions.microsoft.com> skrev i meddelandet
> news:61EAE305-B8CB-4A55-AFE5- 61837E91E231@microso
ft.com...
>
>
>
| |
| Dinesh 2006-01-30, 9:28 am |
| Hi Jatin,
Many thanks for your reply.
However, that was just a simple example I had used. Normally I have quite
complex conditions and then need to get the INTERSECT of the result.
However, I will still try using your idea.
Regards,
Dinesh
"jatinder. singh@clovertechnolo
gies.com" wrote:
> Hi Dinesh,
>
> There is a simple trick we can use to find the Opposite of UNION
> (Intersect)
>
> Select A,B,C,D From
> (
> Select A,B,C,D From Table1
> Union All
> Select A,B,C,D From Table2
> ) XYZ Group by A,B,C,D Having count(*) > 1
>
> I hope this help to solve your problem.
>
> With Warm regards
> Jatinder Singh
>
>
| |
| jatinder.singh@clovertechnologies.com 2006-01-31, 3:24 am |
| Hi Dinesh,
Well if what Cindy suggested is your expected output then it is
Division my Friend. Please have a look at it and tell me if you find it
useful.
Create Table Test (ID Int, Value Char(1))
Insert Into Test Values (1, 'A')
Insert Into Test Values (2, 'A')
Insert Into Test Values (3, 'A')
Insert Into Test Values (4, 'A')
Insert Into Test Values (1, 'B')
Insert Into Test Values (4, 'B')
Insert Into Test Values (1, 'C')
Insert Into Test Values (3, 'C')
Insert Into Test Values (4, 'C')
Insert Into Test Values (1, 'D')
Insert Into Test Values (2, 'D')
Insert Into Test Values (4, 'D')
------ Division Version
Select * From Test Where ID In
(
Select ID From Test
Group By ID Having Count(*) = (Select Count(Distinct Value ) from Test
)
)
Select T1.* From Test T1 Where
T1.ID In (Select T2.ID From Test T2 Where T2.Value = 'A') And
T1.ID In (Select T3.ID From Test T3 Where T3.Value = 'B') And
T1.ID In (Select T4.ID From Test T4 Where T4.Value = 'C') And
T1.ID In (Select T5.ID From Test T5 Where T5.Value = 'D')
Drop Table test
With Wamr regards
Jatinder Singh
| |
| Dinesh 2006-01-31, 3:24 am |
| Hi Jatin,
I normally need to count for records on conditions as cited below:-
COUNT FOR (('FA01'$Value AND 'FA02'$Value) OR 'FA03'$Value) AND NOT
(('FB01'$Value OR 'FB02'$Value) AND 'FB03'$Value)
This I could do easily using the COUNT command with my old structure. Now
with SELECT-SQL i feel this could get a bit quite complex.
However I have to agree that keeping in mind that the database can keep
growing to over a million unique records, I have no choice but to adapt the
new structure for speed purpose.
Thanks and Regards,
Dinesh
"jatinder. singh@clovertechnolo
gies.com" wrote:
> Hi Dinesh,
>
> Well if what Cindy suggested is your expected output then it is
> Division my Friend. Please have a look at it and tell me if you find it
> useful.
>
> Create Table Test (ID Int, Value Char(1))
> Insert Into Test Values (1, 'A')
> Insert Into Test Values (2, 'A')
> Insert Into Test Values (3, 'A')
> Insert Into Test Values (4, 'A')
> Insert Into Test Values (1, 'B')
> Insert Into Test Values (4, 'B')
> Insert Into Test Values (1, 'C')
> Insert Into Test Values (3, 'C')
> Insert Into Test Values (4, 'C')
> Insert Into Test Values (1, 'D')
> Insert Into Test Values (2, 'D')
> Insert Into Test Values (4, 'D')
> ------ Division Version
> Select * From Test Where ID In
> (
> Select ID From Test
> Group By ID Having Count(*) = (Select Count(Distinct Value ) from Test
> )
> )
>
> Select T1.* From Test T1 Where
> T1.ID In (Select T2.ID From Test T2 Where T2.Value = 'A') And
> T1.ID In (Select T3.ID From Test T3 Where T3.Value = 'B') And
> T1.ID In (Select T4.ID From Test T4 Where T4.Value = 'C') And
> T1.ID In (Select T5.ID From Test T5 Where T5.Value = 'D')
>
> Drop Table test
>
> With Wamr regards
> Jatinder Singh
>
>
| |
| Olaf Doschke 2006-02-01, 11:25 am |
| > COUNT FOR (('FA01'$Value AND 'FA02'$Value) OR 'FA03'$Value) AND NOT
> (('FB01'$Value OR 'FB02'$Value) AND 'FB03'$Value)
>
> This I could do easily using the COUNT command with my old structure. Now
> with SELECT-SQL i feel this could get a bit quite complex.
>
> However I have to agree that keeping in mind that the database can keep
> growing to over a million unique records, I have no choice but to adapt the
> new structure for speed purpose.
COUNT FOR can be rushmore optimized as SQL can be optimized.
There is no real big difference in performance.
What can't be optimized is the $ operator. And that can neither be optimized as
a FOR condition nor as a WHERE clause of an SQL-Select.
I fear without a deeper knowledge of what is stored in Value we can't help you
with a one size fits all solution, but only optimize individual selects. A redesign
of the data structure might help.
Bye, Olaf.
| |
| jatinder.singh@clovertechnologies.com 2006-02-02, 3:25 am |
| Hi Dinesh,
It is good that you are looking for performance and ready to
incorporate new changes. But the Division Method given would be able to
track no matter how many distinct value you have . It would be more
easier if we have one table where we store the values which are to be
considered only. Let me make more clear.
Table1
A
B
Table 2
1 A
1 B
1 C
1 D
2 A
2 C
3 A
3 B
3 D
4 A
4 B
Select * From Table2 Where ID In
(
Select T2.ID From table1 T1
Inner Join Table2 T2 On T1.Value = T2.Value
Having Count(*) = (Select Count(*) From Table1)
) And Value In (Select Value From Table1)
This will give the output as
1 A
1 B
3 A
3 B
4 A
4 B
But not 2 as it is not having both the rows A & B.
With Warm regards
Jatinder Singh
| |
| Dinesh 2006-02-03, 11:24 am |
| hi jatinder,
thanks for your help.
rgds,
dinesh
"jatinder. singh@clovertechnolo
gies.com" wrote:
> Hi Dinesh,
> It is good that you are looking for performance and ready to
> incorporate new changes. But the Division Method given would be able to
> track no matter how many distinct value you have . It would be more
> easier if we have one table where we store the values which are to be
> considered only. Let me make more clear.
>
> Table1
> A
> B
>
> Table 2
> 1 A
> 1 B
> 1 C
> 1 D
> 2 A
> 2 C
> 3 A
> 3 B
> 3 D
> 4 A
> 4 B
>
> Select * From Table2 Where ID In
> (
> Select T2.ID From table1 T1
> Inner Join Table2 T2 On T1.Value = T2.Value
> Having Count(*) = (Select Count(*) From Table1)
> ) And Value In (Select Value From Table1)
>
> This will give the output as
> 1 A
> 1 B
> 3 A
> 3 B
> 4 A
> 4 B
> But not 2 as it is not having both the rows A & B.
> With Warm regards
> Jatinder Singh
>
>
|
|
|
|
|