CODEWITHSUNDEEP
sqlsql-serverjoininner-join
Markus Sacramento
Markus Sacramento

Reputation: 364

SQL Two JOINS same table different values

I am making multiple inner join:s in order to get data out of database. Depending on what RelationshipType I choose to use the outcome changes.

INNER JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt ON rel.TargetEntityId = BMEt.BaseManagedEntityId
INNER JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT ON rel.RelationshipTypeId = RT.RelationshipTypeId 
    AND RT.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasLocation'

AND

INNER JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt ON rel.TargetEntityId = BMEt.BaseManagedEntityId
INNER JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT ON rel.RelationshipTypeId = RT.RelationshipTypeId 
    AND RT.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasOrganization'

The value I want on both above JOINs is in BMEt.Name.

Iv'e tried to just change the names BMEt to BMEt2, RT to RT2 for the second one and hoped that would do it. But then I get no data. (no errors just no data)

I've also tried to use IN like this:

INNER JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt ON rel.TargetEntityId = BMEt.BaseManagedEntityId
INNER JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT ON rel.RelationshipTypeId = RT.RelationshipTypeId 
    AND RT.RelationshipTypeName IN ('Cireson.AssetManagement.HardwareAssetHasOrganization','Cireson.AssetManagement.HardwareAssetHasLocation')

I then get both values but in the same column obviously. Is it possible to split this into two columns or how should I retrieve the data?

FULL QUERY

DECLARE @OSBuild AS VARCHAR(100)='10.0.19042'

SELECT VRS.[ResourceID] AS 'ResourceID'
       ,VRS.[Name0] AS 'Computername'
       ,VRS.[Resource_Domain_OR_Workgr0] AS 'Domain'
       ,BIOS.SerialNumber0 AS 'Serialnumber'
       ,BMEt.Name AS 'Location'
FROM [SERVER02].[dbo].[v_R_System] as VRS
INNER JOIN [SERVER02].[dbo].[v_GS_OPERATING_SYSTEM] AS vGSOS on vGSOS.[ResourceID] = VRS.[ResourceID] 
INNER JOIN [SERVER02].[dbo].v_GS_PC_BIOS AS BIOS ON BIOS.[ResourceID] = VRS.[ResourceID]
INNER JOIN [SERVER01].[ServiceManager].[dbo].[MT_Cireson$AssetManagement$HardwareAsset] AS HWA ON BIOS.SerialNumber0 = HWA.SerialNumber_C8CF2E89_7A83_1C26_0AD0_887DF9140D5A COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN [SERVER01].[ServiceManager].[dbo].relationship AS rel ON rel.SourceEntityId = HWA.BaseManagedEntityId

INNER JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt ON rel.TargetEntityId = BMEt.BaseManagedEntityId
INNER JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT ON rel.RelationshipTypeId = RT.RelationshipTypeId 
    AND RT.RelationshipTypeName IN ('Cireson.AssetManagement.HardwareAssetHasOrganization','Cireson.AssetManagement.HardwareAssetHasLocation')
    
    WHERE VRS.Operating_System_Name_and0 NOT LIKE '%Server%' AND
        vGSOS.[Version0] = @OSBuild 
            ORDER BY BIOS.SerialNumber0 ASC

SECOND TRY

DECLARE @OSBuild AS VARCHAR(100)='10.0.19042';

with a as (
    SELECT
      VRS.[ResourceID] AS [ResourceID]
      , VRS.[Name0] AS [Computername]
      , VRS.[Resource_Domain_OR_Workgr0] AS [Domain]
      , BIOS.SerialNumber0 AS [Serialnumber]
      , case RT.RelationshipTypeName
          when 'Cireson.AssetManagement.HardwareAssetHasOrganization'
          then 'Organization'
          when 'Cireson.AssetManagement.HardwareAssetHasLocation'
          then 'Location'
        end as RelTypeName
      , BMEt.Name
      
    FROM [SERVER02].[dbo].[v_R_System] as VRS
      JOIN [SERVER02].[dbo].[v_GS_OPERATING_SYSTEM] AS vGSOS
        ON vGSOS.[ResourceID] = VRS.[ResourceID] 
      JOIN [SERVER02].[dbo].v_GS_PC_BIOS AS BIOS
        ON BIOS.[ResourceID] = VRS.[ResourceID]
      JOIN [SERVER01].[ServiceManager].[dbo].[MT_Cireson$AssetManagement$HardwareAsset] AS HWA
        ON BIOS.SerialNumber0 = HWA.SerialNumber_C8CF2E89_7A83_1C26_0AD0_887DF9140D5A COLLATE SQL_Latin1_General_CP1_CI_AS

      JOIN [SERVER01].[ServiceManager].[dbo].relationship AS rel
        ON rel.SourceEntityId = HWA.BaseManagedEntityId
      JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT
        ON rel.RelationshipTypeId = RT.RelationshipTypeId 
          AND RT.RelationshipTypeName IN ('Cireson.AssetManagement.HardwareAssetHasOrganization','Cireson.AssetManagement.HardwareAssetHasLocation')
      JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt
        ON rel.TargetEntityId = BMEt.BaseManagedEntityId
        
    WHERE VRS.Operating_System_Name_and0 NOT LIKE '%Server%'
      AND vGSOS.[Version0] = @OSBuild
)

select *
from a
pivot (
 max(Name) for RelTypeName in (
    [Location],
    [Organization]
  )
) as q
ORDER BY [Serialnumber] ASC

Gives Error:

Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Completion time: 2021-06-24T13:37:17.2405171+02:00

NEW ERROR

Msg 207, Level 16, State 1, Line 40
Invalid column name 'Name'.

Completion time: 2021-06-24T13:58:13.3559097+02:00

Upvotes: 0

Views: 112

Answers (2)

astentx
astentx

Reputation: 6750

You may also use pivot to turn rows to columns, if one RelationshipType may have only one value:

with a as (
    SELECT
      VRS.[ResourceID] AS [ResourceID]
      , VRS.[Name0] AS [Computername]
      , VRS.[Resource_Domain_OR_Workgr0] AS [Domain]
      , BIOS.SerialNumber0 AS [Serialnumber]
      , case RT.RelationshipTypeName
          when 'Cireson.AssetManagement.HardwareAssetHasOrganization'
          then 'Organization'
          when 'Cireson.AssetManagement.HardwareAssetHasLocation'
          then 'Location'
        end as RelTypeName
      , BMEt.Name
      
    FROM [SERVER02].[dbo].[v_R_System] as VRS
      JOIN [SERVER02].[dbo].[v_GS_OPERATING_SYSTEM] AS vGSOS
        on vGSOS.[ResourceID] = VRS.[ResourceID] 
      JOIN [SERVER02].[dbo].v_GS_PC_BIOS AS BIOS
        ON BIOS.[ResourceID] = VRS.[ResourceID]
      JOIN [SERVER01].[ServiceManager].[dbo].[MT_Cireson$AssetManagement$HardwareAsset] AS HWA
        ON BIOS.SerialNumber0 = HWA.SerialNumber_C8CF2E89_7A83_1C26_0AD0_887DF9140D5A COLLATE SQL_Latin1_General_CP1_CI_AS

      JOIN [SERVER01].[ServiceManager].[dbo].relationship AS rel
        ON rel.SourceEntityId = HWA.BaseManagedEntityId
      JOIN [SERVER01].[ServiceManager].[dbo].[RelationshipType] AS RT
        ON rel.RelationshipTypeId = RT.RelationshipTypeId 
          AND RT.RelationshipTypeName IN ('Cireson.AssetManagement.HardwareAssetHasOrganization','Cireson.AssetManagement.HardwareAssetHasLocation')
      JOIN [SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] AS BMEt
        ON rel.TargetEntityId = BMEt.BaseManagedEntityId
        
    WHERE VRS.Operating_System_Name_and0 NOT LIKE '%Server%'
      AND vGSOS.[Version0] = @OSBuild
)

select *
from a
pivot (
  max(Name) for RelTypeName in (
    [Location],
    [Organization]
  )
) as q
ORDER BY [Serialnumber] ASC

db<>fiddle with some simplified example is here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If I understand correctly, you want two sets of joins:

INNER JOIN
[SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] BMEt
ON rel.TargetEntityId = BMEt.BaseManagedEntityId INNER JOIN
[SERVER01].[ServiceManager].[dbo].[RelationshipType] RT
ON rel.RelationshipTypeId = RT.RelationshipTypeId AND
   RT.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasOrganization' INNER JOIN
[SERVER01].[ServiceManager].[dbo].[BaseManagedEntity] BMEt2
ON rel2.TargetEntityId = BMEt2.BaseManagedEntityId INNER JOIN
[SERVER01].[ServiceManager].[dbo].[RelationshipType] RT2
ON rel.RelationshipTypeId = RT2.RelationshipTypeId AND
   RT2.RelationshipTypeName = 'Cireson.AssetManagement.HardwareAssetHasLocation';

One note: Usually this is done using LEFT JOIN rather than INNER JOIN. Without sample data, desired results, and a clear explanation of the query logic it is a bit hard to say, but I'm pretty sure you need outer joins here.

Upvotes: 1

Related Questions