Beginner's SQL question

pankaj_daga · November 11th, 2003, 06:50 PM

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, what I want to do is get the DISTINCZ count of TESTNUMBER, say where
(State = 1, Value = 0) and (State = 2, Value = 1).
So basically this should give me a count of 2 because TestNumber 100 has an entry for (State = 1, Value = 0) and (State = 2, Value = 1).
Also, entry 101 has (State = 1, Value = 0) and (State = 2, Value = 1)
entries. However, I am unable to write this using a query. Please help!

Thanks,
Sincerely,
Pankaj

pankaj_daga · November 12th, 2003, 03:39 AM

Hi,

I think I did not explain my question well. Let me explain the scenario. 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) (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)... So all combinations exist for this state. What SQL construct exists for performing such queries?

I hope this explains my problem a bit more clearly.

Thanks,
Pankaj

pgtips · November 12th, 2003, 05:02 AM

You could put all possible combinations of State and Value into a temporary table (or a permanent table if they are always the same) and join that into the main table, then pull out only those TESTNUMBERs that have all states and values. Something like this:

Code:

-- code here to create temp table and fill it with all values
-- its not clear to me from your post how these values are generated
SELECT TESTNUMBER
FROM yourTable AS T1
INNER JOIN #tempTable AS T2
ON T1.STATE = T2.STATE AND T1.VALUE = T2.VALUE
GROUP BY T1.TESTNUMBER
HAVING COUNT(*) = (SELECT COUNT(*) FROM #tempTable)

pankaj_daga · November 12th, 2003, 07:57 AM

That did the trick! Thank you so much!

Pankaj