如何根据用户输入创建通用查询以返回不同的集合
你好，
如果有人可以帮我解决我的SQL查询，我很感激。
我需要帮助创建一个通用查询，它将根据给定的参数工作。
这是我必须使用的下面的表结构：
表1
MachId
MachCode
MachLinkType（可以是'ROBOT'，'MINI'类型）
表2
StatId
Stat_GDU_Code（链接到表3）
Stat_MachCode（链接到表1）
表3
GDU_Id >
GDU_prefix
GDU_code
GDU_description
注意GDU_CODE不是唯一的。 GDU_prefix和GDU_id必须是唯一的。
表4
PlacId
Plac_Mach_Code（链接到MachCode）
PlacType（例如FROD，PLACK）
PlacDescription
如果MachLinkType是'ROBOT'然后我查看表1和表4以获得描述等：
Hi there,
I appreciate if someone can assist me with my SQL queries.
I require assistance in creating a generic query which will work depending on the parameters given.
Here is my following table structure I have to work with:
Table1
MachId
MachCode
MachLinkType(Can be of Type 'ROBOT', 'MINI')
Table2
StatId
Stat_GDU_Code(linked to the Table 3)
Stat_MachCode(linked to Table1)
Table3
GDU_Id
GDU_prefix
GDU_code
GDU_description
Note The GDU_CODE is not unique. The GDU_prefix and GDU_id have to be unique.
Table4
PlacId
Plac_Mach_Code(links to MachCode)
PlacType(e.g FROD, PLACK)
PlacDescription
If the MachLinkType is 'ROBOT' then I look in Table 1 and Table 4 to get the description etc:
SELECT MachId, MachCode, MachLinkType, PlacType, Plac_Description
from Table1 INNER JOIN Table4
ON Table1.MachCode = Table4.Plac_Mach_Code
RTRIM(MachLinkType)= 'ROBOT' AND RTRIM(PlacType)= 'FROD'
现在如果值不是ROBOT，那么我必须查询3个表（表1，表2和表3）但我有2个问题：
首先，如果我包含GDU_Description字段，我会得到重复的记录。
我尝试在Table1.MachId上使用'distinct'，但我认为这是不正确的方式将它用于我的查询。
我需要表3中关于我想要生成的输出的描述。
以下是我的查询，如果提供的值是用户不是PLAC：
Now if the value is not ROBOT then I have to interrogate 3 tables (Table 1, Table 2 and Table 3) but I have 2 issues with this:
Firstly if I include the GDU_Description fields I get duplicate records.
I have tried using 'distinct' on the Table1.MachId but I think that is incorrect way of using it for my query.
I need the description of the record from Table 3 in regards to the output I am trying to generate.
Below is my query if the value provided by the user isn't PLAC:
SELECT distinct(MachId), MachCode, MachLinkType, Stat_GDU_Code, GDU_Description
from Table1 INNER JOIN Table2
ON Table1.MachCode = Table2.Stat_MachCode
INNER JOIN Table2 ON
Table3.GDU_Code = Table2.Stat_GDU_Code
WHERE RTRIM(MachLinkType) = 'MINIBOT' AND RTRIM(Stat_GDU_Code) = '31A'
其次，如果用户提供ROBOT和MINIBOT类型，我如何以适当的方式组合上面的两个查询，这样我就可以检索ROBOT和MINIBOT类型的结果。
如果有人可以帮助我，我很感激吗？
这是我想在用户控件中生成的那种输出：
MachineReference：123
MachineCode：EPIS
MachineLinkType：AUTONOMOUS
MachinePartCode：31A
MachineDescription：'测试说明'。
无论相应数据位于哪个表中，我都会喜欢使用为这5个标签分配数据。
谢谢，
Secondly, if user provides ROBOT and MINIBOT types how can I combine the two queries above in an appropriate manner so I can retrieve results for both ROBOT and MINIBOT types.
I appreciate if someone can assist me please?
This is the sort of output i want to generate in my user control:
MachineReference:123
MachineCode: EPIS
MachineLinkType: AUTONOMOUS
MachinePartCode: 31A
MachineDescription: 'Test Description'.
Regardless of which table the respective data lives in i would like to use assign the data to those 5 labels.
Thanks,