Hi everyone again, I am stuck ag

[pankajdaga]

Hi everyone again,

I am stuck again. SQL really is not my thing :-( Consider the following table again:

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
8 101 2 0
9 103 1 1
10 103 3 1

Now, I have to get all TESTNUMBER which have different sets of State and Values. So, this table will return 2 sets...

Set 1:

1 0
1 1
2 0
2 1
(Since, 100 and 101 share a common set)

Set 2:
1 1
3 1
(102 does have an identical set as 100 and 101. Common items are allowed but the sets must be identical (same elements))

Any suggestions on how to achieve this?

Thanks a lot,
Pankaj

 

[hwkranger]

you can't do this with a simple sql statement. Nor even a complex one.

at first I was thinking you could concatonate your set together into a string (eg. set1 = 10112021). then group by the strings and then select which strings are the same (strings are concatonated sets) and pull out your sets (which since they are grouped by sets would give you only unique values)

but.

You would have to concatonate your data exactly the same, meaning 10112021 could result in 10112120 but you'd have to make sure that this didn't happen.

You would have to sort your data by TEST then by STATE then by VALUE. Create the concationation based on that sort... then pull the unique sets, and the testnumbers related to them.

I hope this makes sense..

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[JonFer]

To find pairs of TESTNUMBERS with common combos you can do the following. I am assuming again that the TESTNUMBER, STATE, and VALUE key is unique.

Create queryA - join the table to itself on STATE and VALUE and add a criteria that table_1.TESTNUMBER < table_2.TESTNUMBER. Make it a group by query, add the Count(*) field, and add the TESTNUMBER field from each instance of the table (give them aliases like TESTNUMBER1, TESTNUMBER2).

Write queryB that counts the number of rows for each TESTNUMBER and call the count field &quot;Combos&quot;. Add queryB twice to queryA, making 2 tables and 2 queries total now.

In queryA, link the queryB_1.TESTNUMBER to table_1.TESTNUMBER and queryB_2.TESTNUMBER to table_2.TESTNUMBER. In the table_1.Count field in the first query set the criteria to [queryB_1].[Combos] and table_2.Count should be equal to [queryB_2].[Combos]. This will find all pairs of TESTNUMBERS with the exact same combination of elements.

If there could be more than 2 TESTNUMBERS with the same combos, you need to create a new query based on queryA. In the TESTNUMBER1 field (the lower TESTNUMBER), put a criteria:

Not Exists (Select * from queryA as a
where a.TESTNUMBER2=queryA.TESTNUMBER1

The Not Exists will only be true for the lowest TESTNUMBER in a chain of pairs (because it won't be a TESTNUMBER2 value). This query will then list the lowest TESTNUMBER representing a duplicated combo. It will not show you the combos that only appear once but if you get this far, you can probably figure out how to do that on your own.