组合多个SQL JOIN

本文介绍了组合多个SQL JOIN的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

ALTER PROCEDURE [dbo].[GetValues]
@FieldName NVARCHAR(50),
@FormName NVARCHAR(50),
@PoolName NVARCHAR(50)

AS SELECT FieldValue FROM [dbo].[Values]

INNER JOIN [dbo].[Fields]
        ON [dbo].[Fields].FieldID = [dbo].[Values].FieldID

INNER JOIN [dbo].[FormFields]
        ON [dbo].[FormFields].FieldID = [dbo].[Fields].FieldID

INNER JOIN [dbo].[Forms]
        ON [dbo].[Forms].FormID = [dbo].[FormFields].FormID

INNER JOIN [dbo].[Pools]
        ON [dbo].[Pools].FormID = [dbo].[Forms].FormID

WHERE [dbo].[Fields].FieldName = @FieldName
  AND [dbo].[Forms].FormName = @FormName
  AND [dbo].[Pools].PoolName = @PoolName

我希望此代码可以按字段，表单和池名称过滤值".但是它仅按字段名称进行过滤.怎么了?

I expected this code to filter the Values by Field, Form and Pool names. But it only filters by the Field name. What's wrong?

表格

FormID    FormName
96        FormA
98        FormB
97        FormC

游泳池

PoolID    FormID    PoolName
29        96        PoolA1
31        98        PoolB1
30        97        PoolC1

记录

RecordID    PoolID
42          29
43          29
44          29
45          31
46          31
47          31

值

FieldID    RecordID    FieldValue
101        42          Yellow
101        43          Yellow
101        44          Yellow
101        45          Pink
101        46          Pink
101        47          Pink
102        42          Smith
102        43          Jones
102        44          Fletchers
103        42          Fred
103        43          Bob
103        44          Marty

例如，如果我使用收藏夹颜色"(FieldID = 101)，"FormA"(FormID = 96)和"PoolA1"(PoolID = 29)进行过滤，则会显示黄色，黄色，黄色，粉红色，粉红色"，Pink"，而应为"Yellow，Yellow，Yellow".

For example, if I filter with "Favorite color" (FieldID=101), "FormA" (FormID=96) and "PoolA1" (PoolID=29), it shows "Yellow,Yellow,Yellow,Pink,Pink,Pink" whereas it should be "Yellow,Yellow,Yellow".

推荐答案

我认为，问题出在存储过程本身.其他一切都很好.JOIN的处理不正确，这就是我得到错误结果的原因.解决方法如下:

As I thought, the problem was in the stored procedure itself. Everything else was fine. The JOINs were incorrectly done and that's the reason why I got erroneous results. Here's the solution:

ALTER PROCEDURE [dbo].[GetValues]
@FieldName NVARCHAR(50),
@FormName NVARCHAR(50),
@PoolName NVARCHAR(50)

 AS SELECT FieldValue
      FROM [dbo].[Values]

INNER JOIN [dbo].[Fields]
        ON [dbo].[Fields].FieldID = [dbo].[Values].FieldID

INNER JOIN [dbo].[Records]
        ON [dbo].[Records].RecordID = [dbo].[Values].RecordID

INNER JOIN [dbo].[Pools]
        ON [dbo].[Pools].PoolID = [dbo].[Records].PoolID

INNER JOIN [dbo].[Forms]
        ON [dbo].[Forms].FormID = [dbo].[Pools].FormID

     WHERE [dbo].[Fields].FieldName = @FieldName
       AND [dbo].[Forms].FormName = @FormName
       AND [dbo].[Pools].PoolName = @PoolName

这篇关于组合多个SQL JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

forma