→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
harringg
Champion Sweeper
I've got this report which shows device by room:
SELECT TOP 1000000 tsysdevicetypes.itemtypeicon10 AS icon,
tblcustdevices.displayname AS [Device name],
tblcustdevices.model,
tblcustdevices.vendor,
tblcustdevices.location,
tblcustdevices.devicekey,
tblcustdevices.purchasedate,
tblcustdevices.serialnumber,
tblcustdevices.contact
FROM tblcustdevices
INNER JOIN tsysdevicetypes
ON tsysdevicetypes.itemtype = tblcustdevices.devicetype
WHERE tblcustdevices.location = 'Room 150'
ORDER BY tblcustdevices.displayname


I've got this report that shows computers by room:

SELECT TOP 1000000 tblcomputers.computername,
tblcomputers.computerunique,
tblcomputers.domain,
tblcompcustom.purchasedate,
tblcompcustom.warrantydate,
tblcompcustom.location,
tblcompcustom.building,
tblcompcustom.department,
tblcompcustom.branchoffice,
tblcompcustom.state,
tblcompcustom.lastchanged,
tblcompcustom.barcode,
tblcompcustom.custom1,
tblcompcustom.lastsaved,
tblcompcustom.firstseen,
tblcompcustom.lastseen
FROM tblcomputers
INNER JOIN tblcompcustom
ON tblcomputers.computername = tblcompcustom.computername
WHERE tblcompcustom.location LIKE 'Room 150'
ORDER BY tblcomputers.computerunique


Is there a way to merge them into one report? So I can have a report that shows all the devices and computers in the same room (Location)?
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
You need to use 'UNION'

Example

Select field1,field2 from table1
UNION
Select field1,field2 from table2



See: http://msdn.microsoft.com/en-us/library/ms180026.aspx

View solution in original post

11 REPLIES 11
Hemoco
Lansweeper Alumni
jgross wrote:
Is there an update for this for version 5?

Could you please clarify what your specific report should list.
Hemoco
Lansweeper Alumni
You need to use 'UNION'

Example

Select field1,field2 from table1
UNION
Select field1,field2 from table2



See: http://msdn.microsoft.com/en-us/library/ms180026.aspx