CODEWITHSUNDEEP
sqlsql-server
decoder
decoder

Reputation: 926

Row Act like Column Database Query

I have a Person table.Persons are good at some language and i give them weight.My table are as follows:

Person

ID  Name    Description
1   Rodra   Some..
2   Rakib   Some..
3   Samsad  Some..
4   Foysal  Some..

Language
TypeID  TypeName
1   C#  
2   Asp.Net
3   Python
4   JSP
5   Java
6   Jquery
7   Android

PersonSkill

ID  PersonID    TypeID  Weight
1   1           1       60
2   1           3       50
3   1           7       40
4   2           1       80
5   2           2       70
6   3           1       90
7   3           2       50
8   4           1       60
9   4           2       50
10  4           6       40
11  4           7       55

Now i want to query those person who know c#(TypeId 1)>65 and Asp.net(TypeID 2)>65.How to do it?Anyone helps me greatly appreciated.

Upvotes: 1

Views: 78

Answers (2)

Mahipat Kanzariya
Mahipat Kanzariya

Reputation: 148

select p.* from 
person p 
JOIN personSkill ps ON p.ID=ps.PersonID 
JOIN Language l ON l.TypeID=ps.TypeID 
WHERE (l.TypeName = 'c#' AND ps.weight>65) 
AND 
(l.TypeName = 'Asp.net' AND ps.weight>65)

Upvotes: 0

mehdi lotfi
mehdi lotfi

Reputation: 11571

Select * 
FROM person
WHERE EXISTS(SELECT * FROM PersonSkill WHERE PersonId = Person.Id AND TypeID = 1 AND Weight>65)
AND EXISTS(SELECT * FROM PersonSkill WHERE PersonId = Person.Id AND TypeID = 2 AND Weight>65)

Upvotes: 2

Related Questions