Alternative to AND/OR? 2

[FederalProgrammer]

Consider the following data; I need a query that will return ID = 1 only if anotherGroupID = {1, 2, 3}.

id  |  anotherGroupID
----------------------
 1  |   1
----------------------
 1  |   2
----------------------
 1  |   3
----------------------
 2  |   1
----------------------
 2  |   5
----------------------
 2  |   3
----------------------

I've tried:

 Select ID from table where AnotherGroupID = 1 Or 2 Or 3

But this returns 1 and 2!
I've also tried

SELECT 
anotherGroupID,
ID

FROM 
table

GROUP BY 
anotherGroupID, ID

Having
anotherGroupID = 1 OR
anotherGroupID = 2 OR
anotherGroupID = 3

ANY IDEAS?

 

[PHV]

Select ID from table where AnotherGroupID IN (1, 2, 3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FederalProgrammer]

Hay, Thanx for replying....
You have given me a very good alternative!
But unfortunately, it returns both 1 and 2... I only need "1" for the case when anotherGroupID = 1, 2, 3




---------------

http://www.enyotic.com/corporate/

 

[Zathras]

Select ID
from table
where id=1 AND AnotherGroupID IN (1, 2, 3)

 

[FederalProgrammer]

hmmm, thanx.
But you see, the whole point of the query is to return the id of the group (1, 2, 3).
You are assuming that ID is known, whereas, I'm trying to find out what ID is.
The query should be such that, given a list of "anotherGroupID", the associated "ID" can be determined.

Query for "anotherGroupID" = 1, 2, 3 should return 1 (and only 1).
Query for "anotherGroupID" = 1, 5, 3 should return 2 (and only 2).
and so on. The point is, the result of the query will determine what the corresponding id is.

 

[PHV]

Select ID from table where AnotherGroupID IN (1, 5, 3)
GROUP BY ID HAVING COUNT(*)=3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FederalProgrammer]

hmm
but the problem is the count of anotherGroupID is not always 3...

May the following dataset would be a better example:

id  |  anotherGroupID
----------------------
 1  |   1
----------------------
 1  |   2
----------------------
 1  |   3
----------------------
 2  |   2
----------------------
 2  |   7
----------------------
 2  |   9
----------------------
 2  |   11
----------------------
 3  |   1
----------------------
 4  |   2
----------------------
 4  |   534
----------------------

 

[PHV]

May the following dataset would be a better example
And what is the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zathras]

So, continuing with PHV's answer:

Select ID from table where AnotherGroupID IN (1, 5, 3)
GROUP BY ID HAVING COUNT(*)=3

Select ID from table where AnotherGroupID IN (2, 7, 9, 11)
GROUP BY ID HAVING COUNT(*)=4

Select ID from table where AnotherGroupID IN (2, 7, 9)
GROUP BY ID HAVING COUNT(*)=3

Select ID from table where AnotherGroupID IN (7, 9, 11)
GROUP BY ID HAVING COUNT(*)=3

Select ID from table where AnotherGroupID IN (2, 11)
GROUP BY ID HAVING COUNT(*)=2

Select ID from table where AnotherGroupID IN (2, 534)
GROUP BY ID HAVING COUNT(*)=2

 

[FederalProgrammer]

Thanx for the replies. But I'm gonna try to be more clear:

I need a query like the following:

Select ID
From Table
Where 
anotherGroupID = $A_Set_of_anotherGroupID

think of $A_Set_of_anotherGroupID as a paramater; It can be anything: {1 2 3} or {53, 44, 39}.
$A_Set_of_anotherGroupID = {1 2 3} -> 1
$A_Set_of_anotherGroupID = {2 534} -> 4
$A_Set_of_anotherGroupID = {1 2 534} -> NO RESULTS
$A_Set_of_anotherGroupID = {1 2 3 534} -> NO RESULTS

Also, to be more clear, $A_Set_of_anotherGroupID, is not really a parameter to the query. I am writing this query in .NET; so obviously I can create the condition accordingly.

I hope now you see that, COUNT cannot be usefull (because it is impossible to tell what COUNT(*) should be equal to!)



---------------

http://www.enyotic.com/corporate/

 

[FederalProgrammer]

Thanx for the replies. But I'm gonna try to be more clear:

I need a query like the following:

Select ID
From Table
Where 
anotherGroupID = $A_Set_of_anotherGroupID

think of $A_Set_of_anotherGroupID as a paramater; It can be anything: {1 2 3} or {53, 44, 39}.
$A_Set_of_anotherGroupID = {1 2 3} -> 1
$A_Set_of_anotherGroupID = {2 534} -> 4
$A_Set_of_anotherGroupID = {1 2 534} -> NO RESULTS
$A_Set_of_anotherGroupID = {1 2 3 534} -> NO RESULTS

Also, to be more clear, $A_Set_of_anotherGroupID, is not really a parameter to the query. I am writing this query in .NET; so obviously I can create the condition accordingly.

I hope now you see that, COUNT cannot be usefull (because it is impossible to tell what COUNT(*) should be equal to!)

 

[PHV]

So, in .NET you can't count the number of values in $A_Set_of_anotherGroupID ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FederalProgrammer]

hehehe, I like your sarcasm. Of course i can
but consider the following case:

id  |  anotherGroupID
----------------------
 1  |   1
----------------------
 1  |   2
----------------------
 2  |   1
----------------------
 2  |   3
----------------------
 3  |   1
----------------------
 3  |   7
----------------------

do you think the count can help me here?

 

[PHV]

I really don't see where is the new problem in your last sample data.
Provided your .NET code build on the fly the SQL code, you simply have to do something like this:
SELECT ID FROM table WHERE AnotherGroupID IN (comma separated list of the values in $A_Set_of_anotherGroupID)
GROUP BY ID HAVING COUNT(*)=number of values in $A_Set_of_anotherGroupID


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FederalProgrammer]

well, if you run the following query
Select ID from table where AnotherGroupID IN (1, 3)
GROUP BY ID HAVING COUNT(*)=2

will return
ID = 1, 2 and 3;

The correct result however, should be 2.


---------------

http://www.enyotic.com/corporate/

 

[FederalProgrammer]

well, if you run the following query
Select ID from table where AnotherGroupID IN (1, 3)
GROUP BY ID HAVING COUNT(*)=2

will return
ID = 1, 2 and 3;

The correct result however, should be 2.

 

[PHV]

And which RDBMS returns such false result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FederalProgrammer]

Try this:

ID anotherGroupID
1 1
1 2
1 3
2 1
2 2
2 5
3 2
3 5
4 3
4 5

and look for
anotherGroupID = 1 or 2



---------------

http://www.enyotic.com/corporate/

 

[PHV]

If ID shouldn't have anotherGroupID outside the list:
SELECT ID FROM table G
WHERE AnotherGroupID IN (comma separated list of the values in $A_Set_of_anotherGroupID)
AND number of values in $A_Set_of_anotherGroupID=(SELECT Count(*) FROM table WHERE ID=G.ID)
GROUP BY ID HAVING Count(*)=number of values in $A_Set_of_anotherGroupID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FederalProgrammer]

it sounds like we're almost there...
I just have difficulty understanding the following:

number of values in $A_Set_of_anotherGroupID

where is this field... Please don't worry about my .net code. I'll translate it to code later. I really think you're onto something... How would this number of values in $A_Set_of_anotherGroupID be done in sql?



---------------

http://www.enyotic.com/corporate/