SQL EXISTS() Query

Person Table
——————————–
| PersonId   |  PersonName       |
——————————–
| 1             |  Abc                  |
——————————–
| 2             |  Pqr                   |
——————————–
| 3             |  Xyz                  |
——————————–


Language Table
——————————–
| LanguageId  | LanguageName  |
——————————–
| 7                |  C#                 |
——————————–
| 8                |  Java               |
——————————–
| 9                |  Python            |
——————————–


PersonSkill Table
—————————————
PersonId  |  LanguageId  | SkillLevel   |
—————————————
| 1            |  7               |  20           |
—————————————
| 1            |  8               |  56           |
—————————————
| 1            |  9               |  60           | 
—————————————


Now i want to query those persons who know C# > 65 and Java > 80.


SQL Statement

SELECT * FROM Person
WHERE 
EXISTS(SELECT * FROM PersonSkill WHERE PersonId = Person.PersonId AND LanguageId  = 7 AND SkillLevel>65)

AND 
EXISTS(SELECT * FROM PersonSkill WHERE PersonId = Person.PersonId AND LanguageId  = 8 AND SkillLevel>80)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s