Crafting a T-SQL Query

Question

I have an admittedly novice question about a T-SQL query (which makes sense since I am indeed a novice when it comes to T-SQL).

Consider the following table --

Key    | fieldName  |    Value
==============================
465    |    Bing    |     10
465    |    Ping    |     50
846    |    Bing    |     20  
846    |    Zing    |     80  
678    |    Bing    |     10
678    |    Ping    |     50
678    |    Zing    |     20 

How would I compose a query to return the following?

• If there exists a row with the fieldName Bing and Value of 10, return all of the rows with that key, otherwise don't return any rows pertaining to that key.

In the above example, the result set should be as follows --

Key    | fieldName  |    Value
==============================
465    |    Bing    |     10
465    |    Ping    |     50
678    |    Bing    |     10
678    |    Ping    |     50
678    |    Zing    |     20 

While I understand that there are likely ways better ways to reorganize the data stored in this table, I do not have control over this. I'm happy to read any comments regarding the reorganization of the data, but I can't mark anything an answer that doesn't solve the problem as it currently exists.

Answer 1

You can join on the table again to find the Bing/10 values:

SELECT DISTINCT T1.[Key], T1.fieldName, T1.Value
FROM YourTable T1
INNER JOIN YourTable T2 ON T1.[Key] = T2.[Key]
WHERE T2.fieldName = 'Bing' and T2.Value = 10

And because they're all the rage right now, here's a SQL Fiddle demonstration.

Answer 2

Another options would be:

SELECT DISTINCT T1.[Key], T1.fieldName, T1.Value
FROM YourTable T1
WHERE EXISTS (SELECT 1 
              FROM YourTable T2
              WHERE T2.[Key] = T1.[Key]
                  AND T2.fieldName = 'Bing'
                  AND T2.Value = 10)