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

Stuck - Cant figure out a Query for this situation

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...

Jan 3 '06 #1
2 1396
Nevermind, I figured it out... its a "Relational Division"

that was a kick in the groin, but I learned something here I think...

Jan 3 '06 #2
Hi Mike

You may want to check out http://www.aspfaq.com/etiquette.asp?id=5006 on how
to post useful DDL and example data. With information in a usable format you
are more likely to get a quicker and accurate answer.

John

"Mike Curry" <mm*****@rogers.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Nevermind, I figured it out... its a "Relational Division"

that was a kick in the groin, but I learned something here I think...

Jan 3 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: chris | last post by:
a short while ago i was playing arround with the <DIV tag and wanted some text to dissapear - while i was doing this i had a situation where i could hide the info i wantd to hide but it would move...
5
by: Dimitri Furman | last post by:
Using SQL Server 2000 SP4. There is a relatively complex stored procedure that usually completes in less than 20 seconds. Occasionally it times out after 180 seconds. The SP is called via ADO...
17
by: so many sites so little time | last post by:
all right so the script is pretty simple it goes it retrives what the id of the post is and it lets you edit it well no it doesnt. now if you go to www.kirewire.com/pp2/index/php you will see a...
10
by: Cliff | last post by:
Greetings, I have been trying to teach myself C++ over the past few weeks and have finally came across a problem I could not fix. I made a simple program that prints out a square or rectangle...
6
by: StephQ | last post by:
I need to implement an algorithm that takes as input a container and write some output in another container. The containers involved are usually vectors, but I would like not to rule out the...
1
by: Rahul Babbar | last post by:
Hi, I am having a difficult situation in here... Seems there is a query to create an index on a table and has just got stuck..... It is showing that it is in UOW executing status (in DB2...
17
by: Bill Cunningham | last post by:
I have this code and it will not compile telling me that pow is undefined. I'm not quite sure what to make of this so I thought I'd get some feedback. #include <stdio.h> #include <stdlib.h>...
2
by: Mucahit ikiz | last post by:
I cant make a full dynamic query in LINQ I have 2 situation methods (only_exp_query, only_tbl_query) those are working. .... using System.Linq.Dynamic; using System.Data.Linq; .... string...
4
by: MeDontGetSomething | last post by:
Hi What is the easiest way to format c drive from usb drive without using XP instalation disk. That was the shor story, this the long one if someone cares to read:) See, the situation...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.