David_Wallis

After yesterday Ive got the following...

CREATE VIEW [HardwareAudit]
AS
SELECT
PCDetails.Name0 as ComputerTagNumber,
[ATAssets].[Model],
[ATAssets].[SerialNumber],
[ATAssets].[DateOfPurchase],
[ATAssets].[PurchaseValue],
Disk_Data.Size0 as HardDiskSize,
[NetworkData].[IPAddress0],
[NetworkData].[IPSubnet0],
[NetworkData].[MACAddress0],
OperatingSystemData.Name0 as OperatingSystem,
[PC_Memory_Data].[TotalPhysicalMemory0],
[PCDetails].[Domain0],
[PCDetails].[Model0],
[PCDetails].[Manufacturer00],
[Processor_Data].[CurrentClockSpeed0],
[Processor_Data].[Name0],
[TabsLocations].[Description],
[TabsLocations].[Floor],
[TabsDepartments].[Department],
[TabsBuildings].[BuildingName]

FROM [PcDetails] Inner Join
[ATAssets] On
substring(PcDetails.Name0,5,(len(PcDetails.Name0)-4)) = AtAssets.AssetCode
Inner Join [TabsLocations] on [ATAssets].[LocationCode] = [TabsLocations].[LocationCode]
Inner Join [Disk_Data] on [PCDetails].[MachineID] = [Disk_Data].[MachineID]
Inner Join [NetworkData] on [PCDetails].[MachineID] = [NetworkData].[MachineID]
Inner Join [OperatingSystemData] on [PCDetails].[MachineID] = [OperatingSystemData].[MachineID]
Inner Join [PC_Memory_Data] on [PCDetails].[MachineID] = [PC_Memory_Data].[MachineID]
Inner Join [Processor_Data] on [PCDetails].[MachineID] = [Processor_Data].[MachineID]
Inner Join [TabsBuildings] on [TabsLocations].[Building] = [TabsBuildings].[UID]
Inner Join [TabsDepartments] on [TabsLocations].[Department] = [TabsDepartments].[UID]

however if I do the following:

CREATE VIEW [HardwareAudit]
AS
SELECT DISTINCT
PCDetails.Name0 as ComputerTagNumber,
[ATAssets].[Model],
[ATAssets].[SerialNumber],
[ATAssets].[DateOfPurchase],
[ATAssets].[PurchaseValue],
Disk_Data.Size0 as HardDiskSize,
[NetworkData].[IPAddress0],
[NetworkData].[IPSubnet0],
[NetworkData].[MACAddress0],
OperatingSystemData.Name0 as OperatingSystem,
[PC_Memory_Data].[TotalPhysicalMemory0],
[PCDetails].[Domain0],
[PCDetails].[Model0],
[PCDetails].[Manufacturer00],
[Processor_Data].[CurrentClockSpeed0],
[Processor_Data].[Name0],
[TabsLocations].[Description],
[TabsLocations].[Floor],
[TabsDepartments].[Department],
[TabsBuildings].[BuildingName]

FROM [PcDetails] Inner Join
[ATAssets] On
substring(PcDetails.Name0,5,(len(PcDetails.Name0)-4)) = AtAssets.AssetCode
Inner Join [TabsLocations] on [ATAssets].[LocationCode] = [TabsLocations].[LocationCode]
Inner Join [Disk_Data] on [PCDetails].[MachineID] = [Disk_Data].[MachineID]
Inner Join [NetworkData] on [PCDetails].[MachineID] = [NetworkData].[MachineID]
Inner Join [OperatingSystemData] on [PCDetails].[MachineID] = [OperatingSystemData].[MachineID]
Inner Join [PC_Memory_Data] on [PCDetails].[MachineID] = [PC_Memory_Data].[MachineID]
Inner Join [Processor_Data] on [PCDetails].[MachineID] = [Processor_Data].[MachineID]
Inner Join [TabsBuildings] on [TabsLocations].[Building] = [TabsBuildings].[UID]
Inner Join [TabsDepartments] on [TabsLocations].[Department] = [TabsDepartments].[UID]

I dont get the results I expected, im still getting duplicates, is it because of the join type that I am using?? I want one match for each, if that makes sense...

David

GaryK

David,

An inner join will only include matching rows so looks like you are going the right way, what row data is being duplicated?

Looks like the data model could do with revising, getting on the limit with the joins there (hmmm some consultancy needed me thinks !!!)

Gary

David_Wallis

this is what I get..

ComputerTagNumber Model SerialNumber DateOfPurchase PurchaseValue HardDiskSize IPAddress0 IPSubnet0 MACAddress0 OperatingSystem TotalPhysicalMemory0 Domain0 Model0 Manufacturer00 CurrentClockSpeed0 Name0 Description Floor Department BuildingName
CPL025732 £1,500.00 2014 10.0.2.193 255.255.240.0 00:08:C7:50:49:A1 Microsoft Windows NT Workstation|C:\WINNT|\Device\Harddisk0\partition1 64948 VENTURAUK Compaq Deskpro EN Series Compaq Computer Corporation 233 Call Centre Mezz Floor First Fl Call Centre Ventura House
CPL025732 £1,500.00 2014 10.0.2.193 255.255.240.0 00:08:C7:50:49:A1 Microsoft Windows NT Workstation|C:\WINNT|\Device\Harddisk0\partition1 64948 VENTURAUK Compaq Deskpro EN Series Compaq Computer Corporation 233 Call Centre Mezz Floor First Fl Call Centre Ventura House
CPL028576 £875.00 2014 10.0.6.97 255.255.240.0 00:00:F80:E2:9C Microsoft Windows NT Workstation|C:\WINNT|\Device\Harddisk0\partition1 64948 VENTURAUK Compaq Deskpro EN Series Compaq Computer Corporation 233 Call Centre First Fl Call Centre Providence House
CPL028576 £875.00 2014 10.0.6.97 255.255.240.0 00:00:F80:E2:9C Microsoft Windows NT Workstation|C:\WINNT|\Device\Harddisk0\partition1 64948 VENTURAUK Compaq Deskpro EN Series Compaq Computer Corporation 233 Call Centre First Fl Call Centre Providence House
CPL031922 9x150 01/01/1999 £0.00 19092 10.0.7.51 255.255.240.0 00:B004:B3:ED Microsoft Windows NT Workstation|C:\WINNT|\Device\Harddisk0\partition1 259540 VENTURAUK OptiPlex GX150 Dell Computer Corporation 864 General Office Third Fl General Office Hepworth House
CPL031922 9x150 01/01/1999 £0.00 19092 10.0.7.51 255.255.240.0 00:B004:B3:ED Microsoft Windows NT Workstation|C:\WINNT|\Device\Harddisk0\partition1 259540 VENTURAUK OptiPlex GX150 Dell Computer Corporation 864 General Office Third Fl General Office Hepworth House
CPL031985 1 2001 29/08/2001 £0.00 9538 Microsoft Windows 2000 Professional|C:\WINNT|\Device\Harddisk0\Partition1 129456 VENTURAUK Deskpro Compaq 731 1st Floor Link First Fl Corridor Providence House
CPL031985 1 2001 29/08/2001 £0.00 9538 Microsoft Windows 2000 Professional|C:\WINNT|\Device\Harddisk0\Partition1 129456 VENTURAUK Deskpro Compaq 731 1st Floor Link First Fl Corridor Providence House

David_Wallis

or..



David

GaryK

David,

easy just process the result set stripping out every second record!

Seriously though, can't tell from that, I would need to see how the tables are related to be of any help.

Gary

Jerome

Try adding:-

WHERE Name0 <> NULL

Belf

would a GROUP BY clause not sort it