What kind of SQL statement can do this job?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:
I have a table (actually view) like this:
name1 number1
A 1
A 2
A 3
A 4
B 5
B 4
B 7
B 3
C 3
C 8
D 4
D 2
E 1
E 2
....
this is a cross table. I am looking for a query, which name1 have number1 3
and 4. the correct answer will be A and B. C only have 3, D only have 4.
I tried
WHERE NUMBER1 IN (3,4)
which will return A, B, C, D.

Did anybody have the same problem before? where is the good place to ask
this kind of question? I would really appreaciate you help.


I use Oracle 9i as backend, Access XP as front end. I use link table to link
some Oracle table and view to Access.
 
Try something along the lines of
SELECT name1
FROM YourTable
WHERE number1 IN (3,4)
HAVING COUNT(number1) = 2

Hope This Helps
Gerald Stanley MCSD
 
Gerald,

I was working on this problem, and thought of the same
code. The problem I would think is that this requires the
numbers to be predetermined, as well as the number of
them. As soon as you allow a parameter, the whole thing
bogs down. If that is fine for the OP, then problem
solved.

As soon as I tried various solutions, I ran across "Field
to Complex" errors. I'm still looking at different
solutions.


Chris Nebinger
 
Thank you, Gerald:
I found this code work,
SELECT name1
FROM YourTable
WHERE number1 IN (3,4)
GROUP BY name1
HAVING COUNT(name1) = 2
when I began work on my real data, some problem come up. this data is from
a view which there are about 8 tables join togerher (inner join and outer
join) . because there are other join exist, "HAVING COUNT(name1) = 2" does
not work, so I can it to "HAVING COUNT(name1) >= 2" which grab more data than
I need. So is there any other way to make this work?

Thank you and appreciate your help.


Wes
 
Dear Wes:

A possible problem would be that the values in the column [number1]
may not be unique for each value of [name1]. You could have A/3 twice
and not have A/4, for example, and still A would be returned. If this
is the case, you may need to put a DISTINCT subquery in the FROM
clause for this:

SELECT name1
FROM (SELECT DISTINCT name1, number1 FROM YourTable) T
WHERE number1 IN (3, 4)
HAVING COUNT(*) = 2

Perhaps there could be some other cause of your problem, but this
would seem to be a possibility.

The method of using the IN() with a COUNT() presumes each element is
unique, so that when you find the required COUNT you have ensured
there is at least one of each value.

It is not unlikely this may slow things.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear Chris:

You could not generally create a list of values for the IN() statement
sing a parameter. If you need to work with a list of variable lenght,
it would be good to put this list into a temporary (local) table and
JOIN to it, using a subquery count of the number of rows found for the
HAVING clause.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top