如何根据两个属性编写查询以提取值？ [英] How Do I Write A Query For Extracting Values Based On Two Attributes?

问题描述

我有五个表：

DeviceInfo，DefinitionSetting，DeviceDefinitionSettingValue，DeviceSettingXref，DeviceDefinitionCategory。



DeviceInfo具有以下属性

I have five tables :
DeviceInfo,DefinitionSetting,DeviceDefinitionSettingValue,DeviceSettingXref, DeviceDefinitionCategory.

DeviceInfo Has these attributes

di_DeviceInfoId (Primary key)
di_LogicalDeviceId
di_DeviceDefinitionId
di_DeviceType
di_AssociationId
di_MACAddress
di_IPAddress
di_GatewayAddress
di_SubnetMask
di_LastIPAddress
di_SubnetAddress
di_HostName
di_SerialNumber
di_SysObjectID
di_Location
di_XeroxAssetNumber
di_Firmware
di_SysUpTime
di_DiscoveryDate
di_LastComm
di_IpAddressChanged
di_DiscoveryType
di_Description
di_DiscoveryMethod
di_ProtocolVersion
di_LastModified
di_HasBeenExported
di_Username
di_DNSName
di_OldMACAddress
di_SysLocation
di_LastCommAttempt
di_TimeZone
di_pwd
di_createdDate
di_StatusId
di_ServiceStatus
di_StatusDate
di_DeviceName
di_Model
di_Manufacturer
di_SoftwareVersion
di_uniqueId
di_InstanceID
di_GetStatusRecievedFromGSM
di_IconStatus

DeviceDefinitionSettingXref具有以下属性：

DeviceDefinitionSettingXref has these attributes:

DeviceDefinitionSettingXrefID (primary key)
DeviceDefinitionID
DeviceDefinitionSettingID
DeviceDefinitionCategoryID
CreatedBy
CreatedDate
ModifiedBy
ModifiedDate

DeviceDefinitionSettingValue有这些

DeviceDefinitionSettingValue has these

DeviceDefinitionSettingValueID (primary key)
DeviceInfoId
DeviceDefinitionSettingXrefID
DeviceDefinitionSettingId
DeviceDefinitionSettingValue
CreatedBy
CreatedDate
ModifiedBy
ModifiedDate
DeviceDefinitionSettingDefaultValue
HasBeenExportedOnce

DeviceDefinitionSetting包含以下内容：

DeviceDefinitionSetting has these:

DeviceDefinitionSettingID (primary key)
SettingName
SettingDescription
IsInitialDeviceSetting
SettingNameEnum
SettingDescriptionEnum
CreatedBy
CreatedDate
ModifiedBy
ModifiedDate
IsReadOnly
ExportedToDevice
SettingLabel
DisplayOrder

最后

DeviceDefinitionCategory具有这些值

and lastly
DeviceDefinitionCategory has these value

DeviceDefinitionCategoryID (primary key)
DeviceDefinitionCategoryType
DeviceDefinitionCategoryName
DeviceDefinitionCategoryDescription
DeviceDefinitionCategoryNameEnum
DeviceDefinitionCategoryDescriptionEnum
CreatedBy
CreatedDate
ModifiedBy
ModifiedDate

我必须提取根据从某个摄像头传递的IP地址，从DeviceDefinitionSetting设置名称并从DeviceDefinitionSettingValue设置值，该摄像头将填充在DeviceInfo表，基于CategoryId，它将在DeviceDefintionCategory中。





我开发了这个查询来做上述事情：

I have to extract setting name from DeviceDefinitionSetting and setting value from DeviceDefinitionSettingValue based on the IP address passed from a certain camera which will be populated in DeviceInfo table and on basis of CategoryId which will be in DeviceDefintionCategory.


I developed this query to do the above:

SELECT     DeviceDefinitionSetting.SettingName, DeviceDefinitionSettingValues.DeviceDefinitionSettingValue
FROM         DeviceDefinitionSettingValues INNER JOIN
DeviceDefinitionSetting ON DeviceDefinitionSettingValues.DeviceDefinitionSettingId = DeviceDefinitionSetting.DeviceDefinitionSettingID
INNER JOIN DeviceDefinitionSettingXref ON DeviceDefinitionSettingValues.DeviceDefinitionSettingXrefID = DeviceDefinitionSettingXref.DeviceDefinitionSettingXrefID AND
DeviceDefinitionSetting.DeviceDefinitionSettingID = DeviceDefinitionSettingXref.DeviceDefinitionSettingID INNER JOIN
DeviceDefinitionCategory ON DeviceDefinitionSettingXref.DeviceDefinitionCategoryID = DeviceDefinitionCategory.DeviceDefinitionCategoryID INNER JOIN
DeviceInfo ON DeviceDefinitionSettingValues.DeviceInfoId = DeviceInfo.di_DeviceInfoId  where di_IPAddress='<some ip address>' and DeviceDefinitionCategory.DeviceDefinitionCategoryID='<some category ID>';

但我不确定，因为这会获取我所需的数据。

欢迎任何建议或备用查询

But i am not sure as this would fetch me required data.
Any Suggestions or alternate query is welcomed

推荐答案

请通过运行确认查询再一次。如果重复记录是因为存在重复值，那么前缀1 / distinct与group by条件将帮助您消除重复。

Please confirm by running the query once again. If the duplicate records are because there are duplicate values, in that case a top 1/distinct with a group by condition will help you eliminate the duplicates.

这篇关于如何根据两个属性编写查询以提取值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！