Combining object and property

user_14806422 0
Viewed 106

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?

tsql sql-server sql database
2 Answers

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');
user_11274280

I will help you to figure how to solve this problem for now but you need to see a SQL tutorials,

So first of all you need in the O/P you want to select both EyeColor and Height for the person so how can you figure out that this value is for the specific person you need to check for that.

It will be a query like this:

SELECT Objects.Name AS NAME, EyeColor.Value AS EyeColor, Height.Value AS Height  
FROM Objects AS Obj  
INNER JOIN Data AS EyeColor ON EyeColor.ObjectID = Obj.ID  
AND EyeColor.PropertyID = 1  
INNER JOIN Data AS Height ON Height.ObjectID = Obj.ID  
AND Height.PropertyID = 2;

You need to test it and modify it.

Note that: there are a lot of other solution so always try to figure what you want well first and then start divide the problem and then try to start writing your query.

Check @Dale K solution too at the same question as it can help you a lot.

user_151533260
Related