Transact-SQL Query / How to combine multiple JOIN statements?

Question

I'm banging my head on this SQL puzzle since a couple of hours already, so i thought to myself : "Hey, why don't you ask the Stack and allow the web to benefit from the solution?"

So here it is. First thing, these are my SQL tables:

Fields

FieldID INT (PK)
FieldName NVARCHAR(50) (IX)

FormFields

FieldID INT (FK)
FormID INT (FK)

Values

FieldID INT (FK)
RecordID INT (FK)
Value NVARCHAR(1000)

Forms

FormID INT (PK)
FormName NVARCHAR(50) (IX)

Records

RecordID INT (PK)
FormID INT (FK)
PoolID INT (FK)

DataPools

PoolID INT (PK)
FormID INT (FK)
PoolName NVARCHAR(50) (IX)

Consider the following constraints.

• Each Form has 0 or more DataPool. Each DataPool can only be assigned to one Form.
• Each Form has 0 or more Field. Each Field might be assigned to several Form.
• Each Record has 0 or more Value. Each Value is linked to a single Record.
• Each DataPool has 0 or more Record. Each Record is linked to a single DataPool.
• Each Value is linked to one Field.
• Also, all the Name columns have unique values.

Now, here's the problem:

I need to query evey value form the Values table based on the following columns:

• The Name of the Field linked to the Value
• The Name of the DataPool linked the Record linked to the Value
• The Name of the Form linked to that DataPool

The 3 columns above must be equal to the 3 received parameters in the stored procedure.

Here's what I got so far:

CREATE PROCEDURE [GetValues]
@FieldName NVARCHAR(50),
@FormName NVARCHAR(50),
@PoolName NVARCHAR(50)

AS SELECT Value FROM [Values]
JOIN [Fields]
ON [Fields].FieldID = [Values].FieldID
WHERE [Fields].FieldName = @FieldName

How can I filter the rows of the Values table by the PoolName column? The DataPools table isn't directly related to the Values table, but it's still related to the Records table which is directly related to the Values table. Any ideas on how to do that?

Answer 1

I feel like I am missing something in your question. If this solution is not addressing the problem, please let me know where it is missing the issue.

SELECT
    Values.Value
FROM
    Values INNER JOIN Fields ON
        Values.FieldId = Fields.FieldId  
    INNER JOIN FormFields ON
        Fields.FieldId = FormFields.FieldId  
    INNER JOIN Forms ON
        FormFields.FormId = Forms.FormId
    INNER JOIN DataPools ON
        Forms.FormId = DataPools.FormId
WHERE
    Fields.FieldName = @FieldName
    AND
    Forms.FormName = @FormName
    AND
    DataPools.PoolName = @PoolName;

Answer 2

if i understand what your needing this should work just fine.

select * from values v

join records r
on v.recordid = r.recordid
join datapool dp 
on r.poolid = dp.poolid
join forms f
on r.formid = f.formid
join fields fi
on v.fieldid = fi.fieldid

where
    fi.FieldName = @FieldName
    AND
    f.FormName = @FormName
    AND
    dp.PoolName = @PoolName;