Combining object and property

Question

I have 3 tables.

First table name: Objects

ID	Name
1	Ahmet
2	Hasan

Second table name: Properties

ID	Name
1	EyeColor
2	Height

Third table name: Data

ObjectID	PropertyID	Value
1	1	Blue
1	2	1.70
2	1	Green
2	2	1.90

Using the above three tables, I want to create the following list with SQL.

Name	EyeColor	Height
Ahmet	Blue	1.70
Hasan	Green	1.90

I tried this transmission as sample SQL

SELECT Distinct Objects.Name, (SELECT * FROM Datas where PropertyID = Datas.PropertyID )
FROM Datas
INNER JOIN Objects ON Datas.ObjectID = Objects.ID
INNER JOIN Properties ON Datas.PropertyID = Properties.ID

Can someone help me please?

Answer 1

Start from the table that has the rows you want in your final result i.e. Objects (although I would strongly recommend never using this as a user table name since its also a system table name).

Then join on the information you need for your other columns.

I highly recommend the use of table aliases to make your query easier to read and understand.

select [Name], E.[Value] EyeColor, H.[Value] Height
from dbo.[Objects] O
inner join dbo.[Data] H on H.ObjectID = O.id
    and H.PropertyID = (select P.id from dbo.Properties P where P.[Name] = 'Height')
inner join dbo.[Data] E on E.ObjectID = O.id
    and E.PropertyID = (select P.id from dbo.Properties P where P.[Name] = 'EyeColor');