Number with multiple points 2

[nlbertram]

Hello---

Here is an example of what I am trying to do. I would like to find every unique "Number" that has a specific set of "Points."


For instance, the unique Number values that have Points =5 And Points = 7 in this example would be:

- 100019
- 334567


Row ID Number Points
1 100019 5
2 100019 6
3 100019 7
4 100019 8
5 100019 9
6 704561 5
7 334567 1
8 334567 5
9 334567 7
11 628499 7
12 558990 3
13 558990 4
14 558990 5
15 558990 6



Any ideas?

 

[BigRed1212]

try:

select number from
(SELECT number
from yourtable
where points=5)
where number in
(SELECT number
from yourtable
where points=7)

 

[MajP]

Another
SELECT
distinct tblData.lngNumber
FROM
tblData
WHERE
tblData.lngNumber In (select lngNumber from tblData where points = 5) And tblData.lngNumber In (select lngNumber from tbldata where points = 7);

 

[PHV]

SELECT Number
FROM yourTable
WHERE Points IN (5, 7)
GROUP BY Number
HAVING COUNT(*)=2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traingamer]

PHV's response assumes that you can not have repeated instances of 5 and/or 7 points for a single number.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[nlbertram]

That is correct, there will not be repeated instances of 5 and/or 7 for a given Number.

I have a follow up question though...


It is possible to use PHV's approach to search for the following?

Points=5 AND (Points=6 OR Points=7)



-

 

[PHV]

SELECT A.Number
FROM yourTable A INNER JOIN (
SELECT DISTINCT Number FROM yourTable WHERE Points IN (6, 7)
) B ON A.Number = B.Number
WHERE A.Number = 5


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nlbertram]

Hi PHV ---

Thanks again for your help. However, I am having some trouble with your last post. I am looking to the following Numbers to be returned.

- 100019
- 334567
- 558990


Am I missing something?


SELECT A.Number
FROM yourTable A INNER JOIN (SELECT DISTINCT Number FROM yourTable WHERE Points IN (6, 7)) B ON A.Number = B.Number
WHERE A.Number = 5