SQL query dilemma. Multiple ANDs on a field over many entries 1

[pankajdaga]

Hi everyone,

I have not been in touch with SQL for a long time. I had to start using it again since a few weeks and it has been quite a struggle.

I have a question and I would be really grateful if someone can help me with it. Consider the following table structure:

ID TESTNUMBER STATE VALUE
1 100 1 0
2 100 1 1
3 100 2 1
4 100 2 0
5 101 1 1
6 101 1 0
7 101 2 1

Now, I have a code that has to generate all possible combinations of chosen States and Values.. Say for our example the combinations are (State = 1, 0) and (State = 2, 1).

Now, I have to go in the database and find the TestNumber which has an entry for (State = 1, 0) AND (State = 2, 0) (which would only be TestNumber 100. If I had used an OR in my Select query it would return both 100 and 101. Also, if I use AND for the same field it would return me nothing)... What SQL construct exists for performing such queries? I hope the question is not too vague. This is really urgent and I tried all of last night, but to no avail. Please help!

Thanks,

Sincerely,
Pankaj

 

[JonFer]

Put the State/Value pairs you want into a 2 field table. In your example the table would have 2 rows:

1 0
2 1

Join this table to your combinations table on STATE and VALUE, group by the TESTNUMBER, and add a Count(*) field. As a criteria for the Count(*) field, enter

&quot;(Select Count(*) from <table>&quot; where <table> is the new table with the desired State/Value pairs.

This will find all TESTNUMBERS which map to all desired State/Value pairs. Make sure TESTNUMBER, STATE, VALUE is unique in your combinations table and that STATE, VALUE is unique in the new table.

 

[pankajdaga]

Hi,

Thank you so much for replying. Could you by any chance give me some SQL code to achieve it. As I mentioned, I am not very good at it.

Thanks,

Sincerely,
Pankaj

 

[JonFer]

I did provide some SQL

Anyway, you should give it a try first on your own. Create a query with the query designer, add both tables and join them by the STATE and VALUE fields. Change it to a &quot;Group By&quot; (sigma button on toolbar) and add the Count(*) field. Add the criteria I mention above and try it out.

If you have problems, look at the SQL for your query and post it here.

 

[JonFer]

I did provide some SQL

Anyway, you should give it a try first on your own. Create a query with the query designer, add both tables and join them by the STATE and VALUE fields. Change it to a &quot;Group By&quot; (sigma button on toolbar) and add the Count(*) field. Add the criteria I mention above and try it out.

If you have problems, look at the SQL for your query and post it here.

 

[pankajdaga]

excellent! It works!

Thanks a bunch man!

Pankaj