I'm attempting to join in data from 3 tables (alm_asset, cmdb_model_category, and sys_user).
Each asset is assigned to a user and each asset has a model category.
I want the dataview to pull in all asset data, but only for users that don't have a 'Computer' asset.
Additionally, I want the dataview to display one row for each user that doesn't have any assigned assets. I suspect this will need to be done with an additional Left join on the sys_user table.
Currently, the database view simply pulls in all non-'Computer' assets that are assigned to a user. But I'd like to filter out any rows which reference any user that has a 'Computer' asset.
Let me know if you could have any ideas on how to make this work or if it's even possible. Thanks
I would have thought that this is not possible, thinking Database Views are pretty dumb when it comes to SQL support. But it somehow works on my instance. Don't know how robust the solution is, future support wise.
But here's what I did: 1st of all, if you want all user, then the "main" table must be User. And the rest is pretty str8 fwd:
The where clause reads:
a1.assigned_to = u1.sys_id && (a1.sys_id is null || exists (select null from cmdb_model_category mc1 where mc1.sys_id = a1.model_category and mc1.name != 'Computer'))As you suspected Left join has to be set to true for table alm_asset/a1.
Table sys_user/u1 has fields sys_id and name defined:
Table alm_asset/a1 has fields asset_tag, assigned_to, display_name, model_category and sys_id defined:
and the result:
and with the restriction to 'Computer' removed:
as you can see, in both cases "survey user" shows up once, with no "Display name" (asset) and no assigned to, as it has no assets. In the 1st case "Abel Tuter" shows two assets, in the 2nd case "Abel Tuter" shows three assets - now the Computer also shows up.
This has been "engineered" in a San Diego instance, so in other versions your mileage may vary. But hope it helps. š
I just now realize I misunderstood the original requirements and so the solution was not the correct one.
The correct solution is to define the view as:
where the condition for table sys_user u1 is:
u1.active = true AND
NOT EXISTS (
SELECT
u3.sys_id AS sys_id
FROM
sys_user AS u3,
alm_asset AS a2,
cmdb_model_category AS c1
WHERE
a2.assigned_to = u3.sys_id AND
c1.sys_id = a2.model_category AND
c1.name = 'Computer' AND
u3.sys_id = u1.sys_id
)This will show users who don't have any assets or users and their assets only if none of those assets is a computer asset.
E.g. the result set when Miranda Hammitt has no assets:
When a consumable is assigned to her:
When in addition a computer is assigned to her:
