den2005


Hi everybody,

How would I get selected columns of both table of all records/rows of FieldType table for specific employee With the simplified Table structure I tried following sql statement. Thanks in advanced.

Sql statements

Select V.FieldValueID, V.FieldID, V.EmployeeID, T.[Name] As 'Field Type',
F.[Name] As 'Field Name', F.DisplayOrder, V.value From Field F Left Join FieldValue V
On V.FieldID = F.FieldID Left Join FieldType C On F.FieldTypeID = T.FieldTypeID
Where V.EmployeeID = 8

Actual Result: (2 records only)

FieldValueID FieldID EmployeeID Field Type Field Name DisplayOrder Value
3 2 8 TextBox Gender 1 F
4 3 8 ComboList MaritalStatus 2 M

Correct Desired Result:(5 Records)

FieldValueID FieldID EmployeeID Field Type Field Name DisplayOrder Value
3 2 8 TextBox Gender 1 F
4 3 8 ComboList MaritalStatus 2 M
NULL 4 NULL TextBox YearsExperience 3 NULL
NULL 5 NULL TextBox NumberOfChildren 4
NULL 1 NULL TextBox Color 5


Table FieldType

FieldTypeID Name
1 TextBox
2 ComboList


Table Field

FieldID FieldTypeID Name DisplayOrder
1 1 Color 5
2 2 Gender 1
3 2 MaritalStatus 2
4 1 YearsExperience 3
5 1 NumberOfChildren 4

Table FieldValue

FieldValueID FieldID EmployeeID Value
1 2 3 M
2 3 3 S
3 2 8 F
4 3 8 M
5 2 10 M
6 4 10 2




Re: Join 3 Tables Return all records even null...

ManiD


The following query will help you,

Select V.FieldValueID, V.FieldID, V.EmployeeID, T.[Name] As 'Field Type',
F.[Name] As 'Field Name', F.DisplayOrder, V.value From Field F Left Join FieldValue V
On V.FieldID = F.FieldID And V.EmployeeID = 8 Left Join FieldType C On F.FieldTypeID = T.FieldTypeID







Re: Join 3 Tables Return all records even null...

Mark - SQL

select v.FieldValueID,
f.FieldID,
v.EmployeeID,
t.Name as [Field Type],
f.Name as [Field Name],
f.DisplayOrder,
v.Value
from Field f
inner join FieldType t on t.FieldTypeID=f.FieldTypeID
left outer join FieldValue v on v.FieldID=f.FieldID and v.EmployeeID=8
order by f.DisplayOrder






Re: Join 3 Tables Return all records even null...

den2005

Thanks ManiD for replying,

Thanks Mark for the sql statement, it works correctly.

What if in all three tables I have additional column "IsActive" and still need to get same results only for those records that are active