Complicated Query? Cant figure out what I need...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated Query? Cant figure out what I need...

Author

Topic

mikecurry
Starting Member

2 Posts

Posted - 2006-01-03 : 00:46:36

I have run into a problem, I have 2 fields in my database, both key fields:

Table 1
=====
Field X <key>
Field Y <key>

In field X, there are say about 3 records for each unique Field Y. I let my users query the data base like follows:

Enter the Codes you want: 1000 and 3000 and 8500

So I want to pick up records where there will be the above values for All Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there is even ONE of the X values not matching a record without a matching X value, leave it out.

i.e:
X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB
X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC

When the query runs, I want to see the following records:

X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB

BUT NOT:

X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC

because one of the X values was not matched (the last X value =9999 and not one of the requirements of the search)

So I guess I want something like this:

SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES (X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD

^^ Hope the above makes sense... but I am really stuck. The only other way I think I could do it is, copy all records that match all 3 X values into a temp table, and weed out any that are missing any one of the X values after they are copied but, I am running this on MYSQL 5.0 Clustered, and there is not enough room in memory for it probably... and query time has to remain under a second.

Anyhelp would be appreciated...

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-01-03 : 00:56:36

Relational Division..

http://www.dbazine.com/celko1.html

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 01:05:41

Also



Select * from yourTable T where exists
(
Select FieldY from yourTable 
where FieldX in(1000,3000,8500)
and FieldY=T.FieldY
group by FieldY having count(*)=3
)

Madhivanan

Failing to plan is Planning to fail

mikecurry
Starting Member

2 Posts

Posted - 2006-01-03 : 01:57:14

What would be quicker (quick is very important for this operation) as there would be hundreds of millions of records:

Example 1
=========
Select distinct fieldy from table1 T where exists
(
Select fieldy from table1
where fieldx in('1000','3000','8500')
and fieldy=T.fieldy
group by fieldy having count(*)=3
)

or

Example 2
=========
select fieldx,fieldy from table1 A where a.fieldx='1000'
and exists
(select 1 from table1 b where a.fieldy=b.fieldy and b.fieldx='3000'
and exists
(select 1 from table1 c where c.fieldy=b.fieldy and c.fieldx='8500'
))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 02:33:28

Set the Execution Plan and check
The First query takes minimum time

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 03:02:48

If you want retrieve only filedY then you can replace Example 1 by

Select fieldy from table1
where fieldx in('1000','3000','8500')
group by fieldy having count(*)=3

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources