Search code examples
sqlsql-serverquery-optimization

query optimization for many to many

i have some data like this :

  1. home 1

    1.record 1

    2.record 2

    3.record 3

  2. home 2

    1.record 2_1

    2.record 2_2

  3. home 3

    1.record 3_1

and i have three table identity and identityField and IdentityFieldValue

[sqlServer 2012]

identity 
  identityId

identityField 
  identityFieldid

IdentityFieldValue
   identityId
   identityFieldid
   value

how to fetch records with a one query and use with two for loop ???

for(home in homes)
{
   for(field in home.fields)
   {
   }
}
Solution

  • Hard to know what you're looking for with the limited amount of data provided, is this what you need?

    SELECT *
FROM identity ID

INNER JOIN IdentityFieldValue IFLV
ON IFLV.identityId = ID.identityId

INNER JOIN identityField IFL
ON IFL.identityFieldid = IFLV.identityFieldid