January 2, 2006 at 10:55 pm
I have run into a problem, I have 2 fields in my database, both key
fields:
Table 1
=====
Field X
Field Y
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...
January 2, 2006 at 11:48 pm
MYSQL 5.0 ? Why post the question on a MS SQL Server forum ? You'll likely get answers that use non-MYSQL 5.0 features. For example, does MYSQL implement derived tables like MS-SQL ?
This solution uses a derived table ...
SELECT *
FROM Table1 As t1
INNER JOIN
(
SELECT ColumnY
FROM Table1
WHERE ColumnX In (1000, 3000, 8500)
GROUP BY ColumnY
HAVING COUNT(*) = 3
) dt
ON t1.ColumnY = dt.ColumnY
WHERE t1.ColumnX In (1000, 3000, 8500)
A more flexible solution might use a @table type variable to avoid the hard-coded "3" and repetitive IN()'s, but again, what's the point of proposing such a MS-SQL-centric solution if MYSQL doesn't support the tech ?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply