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]

How would this number of values in $A_Set_of_anotherGroupID be done in sql?
Not in sql but in .NET (reread my post dated 27 Sep 05 15:56)

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

 

[FederalProgrammer]

Darn, that won't do either then...
Try it and you'll see... I've given some test datasets earlier on


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

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

 

[PHV]

Try it and you'll see
Try WHAT to see WHAT ?
What do you really want to do with what ?

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

 

[Zathras]

I'm having a hard time understanding the requirements....
From your 27 Sep 05 17:22 post:

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

What would you consider to be the correct result set? Two rows (1 and 2)? or Three rows (1, 2 and 3)?

 

[PHV]

For me, only one row: 2

 

[FederalProgrammer]

The correct answer would be: 1 if we were looking for {1, 2, 3} and NULL if looking for {1, 2}
ID = 1 is the only group that contains exactly the set of {1, 2, 3}.
ID = 2 doesn't match because of the 5
ID = 3 and ID = 4 don't match (well, it's kinda obviouse why not)

Anyways, as I thought, this really doesn't have a simple solution:
here's what I've done as a work around:

1) Query the list for all the IDs that match $A_Set_of_anotherGroupID
2) All the IDs that are candidate are fetched
3) Now, for each one of the returned IDs, query the list. ID that contains "EXACTLY" all the $anotherGROUPID items is the answer

Example:

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

Let's say we are looking for $anotherGroupID = (1, 2)
We query the list like so:
Select * from table Where anotherGroupID IN (1, 2)
This query will return: ID = 1 2 3

Now another query is required:
dataset1 = Select * From table where ID = 1
dataset2 = Select * From table where ID = 2
dataset3 = Select * From table where ID = 3

Compare(dataset1, $anotherGroupID)
Compare(dataset2, $anotherGroupID)
Compare(dataset3, $anotherGroupID)

dataset1 will be the only dataset that contains 1 and 2 (nothing more and nothing less!!!)
Obviously this is a simple psudo code and some smart IF-ELSE blocks avoid irrelevent code-executions..

Anyways, this was the best I could come up with. But if you have a better solution, feel free to post it here

thanx!!

 

[PHV]

I quite don't understand why you don't implement the suggestion dated 27 Sep 05 17:39.

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 is an impossible query. In that query, you are applying the following filter:

number of values in $A_Set_of_anotherGroupID=(SELECT Count(*) FROM table WHERE ID=G.ID)

I don't see how this filter effects the outcome of the query

Why don't you run your suggested query and see if it provides you w/ the correct result. Run it on the following test dataset:

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

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

$anotherGroupID = {1 2} should return 1 (and only 1).
it shouldn't be 2, because
2-> {1 2 5}
3-> {2 5}
4-> {3 5}
Only ID = 1 matches {1 2} exactly.

 

[PHV]

So, your .NET code should build this SQL:
SELECT ID FROM table G
WHERE AnotherGroupID IN (1,2)
AND 2=(SELECT Count(*) FROM table WHERE ID=G.ID)
GROUP BY ID HAVING Count(*)=2

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

 

[juliane26]

I am not sure I am completely misunderstanding by reading the thread.
But what I am thinking of is something like:
return all ID's , who's groupIDs are all in the set given by the programmer.
From the first question I don't see that the count matters at all, but I am not sure I understand it correctly.

so my suggestion:

SELECT  DISTINCT A.ID
FROM TABLE A
WHERE EXISTS (
    SELECT 1 
    FROM TABLE B
    WHERE B.ID=A.ID
    AND B.ANOTHERGROUPID IN (1,2,3)
 )
AND NOT EXISTS(
    SELECT 1 
    FROM TABLE C
    WHERE C.ID=A.ID
    AND C.ANOTHERGROUPID NOT IN (1,2,3)
)

I am sure PHV and folks can easily optimize my suggestion further, if that is what you are looking for.
Probably the first exist can be put as well in a simple where condition.

Juliane

 

[FederalProgrammer]

AWESOME!!!! PHV, I've tried it, and it seems like your suggestion is working. It is making sence too!! NiCe!!!!

SELECT ID FROM table G 
WHERE AnotherGroupID IN (1,2)
AND 2=(SELECT Count(*) FROM table WHERE ID=G.ID)
GROUP BY ID HAVING Count(*)=2

Juliane26, it was almost working. In fact, I thought that was it, until I tried to grab the ID for the set of {1, 2, 3, 4} from the following list:
ID toppingID
0 0
1 1
1 2
1 3
2 1
2 2
2 5
3 2
3 5
4 1
4 2
4 3
4 4
Too Darn close though!! (it returned both 1 and 4. The right answer is 4)

 

[it2hill]

Here is a stab:

select distinct(t1.ID)
from testTable t1
where EXISTS (select 1 from testTable t2 where t1.ID = t2.ID and t2.anotherID = 1)
AND EXISTS (select 1 from testTable t2 where t1.ID = t2.ID and t2.anotherID = 2)
AND EXISTS (select 1 from testTable t2 where t1.ID = t2.ID and t2.anotherID = 3)
AND EXISTS (select 1 from testTable t2 where t1.ID = t2.ID and t2.anotherID = 4)

Since you build the SQL each time, just add the EXISTS as the are needed.

 

[FederalProgrammer]

Hmmm,
Almost! but not quite

See, your suggestion would work for when the search is for:
{1, 2, 3, 4}; However, it would fail for {1, 2, 3} given the following test-dataset:

ID toppingID
0 0
1 1
1 2
1 3
2 1
2 2
2 5
3 2
3 5
4 1
4 2
4 3
4 4

 

[it2hill]

Ahh, I see. Then you would need to add the Group and count to mine as well.

 

[dnoeth]

This looks like a lab that i do in my trainings involving pizzas and toppings ;-)

One possible solution which is easy to build in a program:

select id
from tab
group by id
having 
  sum(case when anothergroupid in (1, 2, 3, 4) then 1 
        else -1 end) = 4

Dieter

 

[r937]

dieter, wouldn't your pizza toppings example require the use of DISTINCT somewhere? in the HAVING?

otherwise, how do you test for a pizza with pepperoni and double cheese? how do you tell it apart from a pizza with cheese and double pepperoni?



r937.com | rudy.ca

 

[dnoeth]

In my exampe there a PK on (pizza#,topping#), so it's not necessary (and FederalProgrammer's data looked similar), but of course you can enhance it to cover duplicates:

select id
from tab
group by id
having
  count(distinct case when anothergroupid in (1, 2, 3, 4) then anothergroupid end) = 4
and 
  count(case when anothergroupid not in (1, 2, 3, 4) then anothergroupid end) = 0

Dieter