|
|
Posted by Mike Curry on 01/03/06 07: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...
Navigation:
[Reply to this message]
|