CODEWITHSUNDEEP
sqlsql-server
suresh jat
suresh jat

Reputation: 27

A Simple but complex SQL query

I have a very simple MS SQL table with the following data(with column name and datatype):

TableId     PersonName              Attribute           AttributeValue
(int)       (nvarchar 50)           (nvarchar 50)       (bit)
----------- ----------------------- ------------------- --------------
1           A                       IsHuman             1
2           A                       CanSpeak            1
3           A                       CanSee              1
4           A                       CanWalk             0
5           B                       IsHuman             1
6           B                       CanSpeak            1
7           B                       CanSee              0
8           B                       CanWalk             0
9           C                       IsHuman             0
10          C                       CanSpeak            1
11          C                       CanSee              1
12          C                       CanWalk             0

Now, What I need as a result is the unique PersonName that have both Attribute IsHuman and CanSpeak with AttributeValue = 1.

The expected result should be (Must not include C as this one has IsHuman = 0)

PersonName
------------
A
B

Please can any expert help me in writting a SQL Query for this.

Upvotes: 0

Views: 225

Answers (6)

Rajkumar
Rajkumar

Reputation: 3

select personname from yourtablename where personname in ('a','b') group by personname

Upvotes: 0

Andrew Jansen
Andrew Jansen

Reputation: 196

I think two inner joins may give you alright performance depending on indexing and table sizes.

SELECT t.PersonName FROM table t 
INNER JOIN table t2 ON t.PersonName=t2.PersonName AND t3.Attribute = 'IsHuman' AND t2.AttributeValue = 1 
INNER JOIN table t3 ON t2.PersonName=t3.PersonName AND t3.Attribute = 'CanSpeak' AND t3.AttributeValue = 1

or 

SELECT t.PersonName FROM table t 
INNER JOIN table t2 ON t.PersonName=t2.PersonName
INNER JOIN table t3 ON  t2.PersonName=t3.PersonName
WHERE t2.Attribute = 'IsHuman' AND t2.AttributeValue = 1 AND t3.Attribute = 'CanSpeak' AND t3.AttributeValue = 1

This solution could be simplified significantly however should the properties IsHuman and CanSpeak were in separate tables with an linking ID table between them. Sounds like this table could possibly benefit from some normalization.

If you cant progress that, a view may assist in performance. I am at home without SQL installed so I cant verify any performance aspects.

Upvotes: 0

Michael Lorton
Michael Lorton

Reputation: 44376

I actually use this as a screening question for interviews. None of you people would get the job.

OK, maybe you would, but while the strategies you use might or might not work, they aren't generalizable and they miss a basic notion of relational algebra, to wit, aliasing.

The right answer (in the sense that it would make me more likely to employ you as well as the less importance senses that the RDMS's optimizer understands it and it can be extended to other, arbitrarily complex, cases) is

SELECT t1.PersonName 
  FROM MyTable t1, MyTable t2
 WHERE t2.AttributeName = 'CanSpeak' 
       AND t2.AttributeValue = 1
       AND t1.AttributeName = 'IsHuman' 
       AND t1.AttributeValue = 1
       AND t1.PersonName = t2.PersonName;

Upvotes: -1

onedaywhen
onedaywhen

Reputation: 57023

SELECT PersonName 
  FROM MyTable 
 WHERE AttributeName = 'IsHuman' 
       AND AttributeValue = 1
INTERSECT
SELECT PersonName 
  FROM MyTable 
 WHERE AttributeName = 'CanSpeak' 
       AND AttributeValue = 1;

Obviously this approach doesn't 'scale' if the criteria can vary. It could be that the relational operator you require is division, popularly known as "the supplier who supplies all parts", specifically division with remainder.

Upvotes: 3

Dylan Smith
Dylan Smith

Reputation: 22235

SELECT PersonName 
FROM MyTable 
WHERE (AttributeName = 'IsHuman' AND AttributeValue = 1) OR 
      (AttributeName = 'CanSpeak' AND AttributeValue = 1) 
GROUP BY PersonName
HAVING COUNT(*) > 1

or

SELECT PersonName 
FROM MyTable 
WHERE AttributeValue = 1 AND AttributeName IN ('IsHuman', 'CanSpeak') 
GROUP BY PersonName
HAVING COUNT(*) > 1

Upvotes: 2

Alex_L
Alex_L

Reputation: 2666

SELECT PersonName FROM MyTable 
WHERE PersonName IN 
(SELECT T1.PersonName FROM MyTable T1 WHERE T1.Attribute = 'IsHuman' and T1.AttributeValue='1')
AND (Attribute = 'CanSpeak' AND AttributeValue='1')

Upvotes: 1

Related Questions