SQL grouping aggregate for different values

Question

I have a database table that looks like:

ForeignId: int
Key: varchar
Value: varchar

Where ForeignId and Key constitute a unique primary key I can easily determine the total set of keys defined for a given set of document with

SELECT DISTINCT [Key] FROM [Table] WHERE [ForeignId] IN (...)

What I would like to do however is to further distinguish the values of each values of each property in the case where it is the same for every ForeignId (and NULL or some other sentinal value in the case where there the values differ). If I do something like:

ForeignId  Key  Value
1            1      A
1            2      B
1            3      C
2            1      A
2            2      Z
3            1      A
3            2      Z

I want output like:

Key   Value
1     A      -- All 3 are the same
2     NULL   -- More than one distinct value (B, and Z)
3     NULL   -- Defined for only one ForeignId

The best I have been able to come up with is

SELECT [Key], MAX([Value]), MIN([Value]) FROM [Table]
WHERE [ForeignId] IN (...)
GROUP BY [Key]

and looking for any instances where the max and min values returned differ. If they are the same I assume all of the values match, if they differ, I know there are more than one distinct values.

What's missing here is the 3rd part where I need to mark values as differing if any of the individual items doesn't have a definition at all. In my example above, my current code would return the value C for key 3 even though it is not defined for some of the ForeignIds.

Answer 1

Here's the best I can do to understand the situation.

You have Documents. Is this a Documents table?

They Document has a unique Key.

It also may have zero or more associations with (what?) where (What?) is identified by ForeignId. The association between the two has (Value?)

This is all very unclear to me.

Answer 2

Ok - so if I understand your question you need to generate a list containing two columns, key and value.

The list will have a single row for each and every key, the value column can be defined as:

NULL if it is not defined for all ForeignID' NULL if it is defined for all keys but contains multiple values The value if it is defined for all keys, with that value being consistently the same?

So, I think we want to do something like this:

SELECT
    TABLE.KEY,
    data.VAL
FROM
    TABLE
LEFT JOIN
(
   SELECT KEY, VALUE FROM TABLE GROUP BY KEY, VALUE HAVING COUNT(*) = x
) data

where x is the number of distinct keys (which you may want to throw into a subquery).

The left join will ensure you list all keys, the subquery will only return values for the case when the count of the grouping is equal to the number of keys.

I don't have access to sql (am at home, new PC) to test my syntax, but I think the idea should get you on the right path.

If I have misunderstood your question please let me know

Answer 3

I don't think you can do that without using some kind of code (a stored procedure would work) without it being REALLY ugly (and quite possibly slow).

Here is my best attempt. It's rather ugly. I wouldn't want to use this in production. I question how well it performs (time wise). This is MySQL speak, since that's what I'm familiar with

SELECT key, IF(EXISTS (SELECT 1 FROM table t
                        WHERE t.key = key AND t.value != value),
               null, value)
FROM table
GROUP BY key
ORDER BY key

Here are the explanations of the parts:

SELECT 1 FROM table t WHERE t.key = key AND t.value != value

Returns a row if there is another row in the table with the same key but a different value.

The IF checks if the query above returned a row. If it did (the EXISTS works) then return the null (since there is a row with a different value). If it didn't return a row, then they all match and we return the value.

The GROUP BY makes sure this is only executed once for each key.

The ORDER BY makes it pretty.

Answer 4

Chris pretty much had it. Add another group by and you're golden.

SELECT
    TABLE.KEY,
    data.VAL
FROM
    TABLE
LEFT JOIN
(
   SELECT KEY, VALUE FROM TABLE GROUP BY KEY, VALUE HAVING COUNT(*) = x
) data on Table.Key = data.Key
group by Table.Key, data.VAL