Need help with a database view

[Mike Foreman]

I'm thinking this is not possible, because i'm hoping to get an outside join, but here we go. I'd like to create a join that shows all records in the cmdb_ci_facilities_hardware table and join with the cmdb_ci_ups_power_eq table. There is a reference field that both share (location) but that's about the only common field and to be honest it's not always filled. There's a common group in support_group that might also be used. I'm not sure how to write the "where clause" for this, though. Any suggestions? Thank you!

[AshishKM]

Hi @Mike Foreman , 

You're right GlideQuery doesn't directly support OUTER JOINS like traditional SQL. However, you can try using nested queries and union-like logic to simulate an outer join behavior.

-Thanks,

AshishKM

[Shivalika]

Hello @Mike Foreman 

To include all the records of "cmdb_ci_facilities_hardware" - just check the "Left Join" field here - it will automatically add all the records from this table. 

And in the second table where you are talking about conditions...

You can just add like below 👇 

facilities.location == power.location 

Kindly mark my answer as helpful and accept solution if it helped you in anyway. This will help me be recognized for the efforts and also move this questions from unsolved to solved bucket. 

Regards,

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY

[Ankur Bawiskar]

@Mike Foreman 

please share what did you try so far along with screenshots.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Mike Foreman]

Hi Ankur - I posted some screenshots for this issue. Thanks - Mike