Database View with Left Join show unwanted "duplicate" records

[Robert Campbell]

I want to show

1. 1. All cis that
      1. u_core_ci = true
      2. sys_class_name = 'cmdb_ci_service' || sys_class_name = 'u_cmdb_ci_business_application'
   2. If they have a parent ci that is a business application in the ci relationship table, show that relationship, otherwise just show the ci

Table	Active	Left Join	Order	Variable prefix	Where clause
cmdb_ci	true	false	100	c1
cmdb_ci	true	false	150	c2	c1.sys_id=c2.sys_id && c2.u_core_ci=true && (c2.sys_class_name='cmdb_ci_service' || c2.sys_class_name='u_cmdb_ci_business_application')
cmdb_rel_ci	true	true	200	r1	r1.child=c2.sys_id
cmdb_ci_business_app	true	true	300	b3	b3.sys_id=r1.parent

This gives me mostly what I want except that it has some duplicate c2.  I'd like to not have these duplicates.  I think it's because of the last row above.  I tried adding "&& r1.parent.sys_class_name='cmdb_ci_business_app'" 3rd row but that returns no records.

I ran the query and got the results I wanted in mysql like this:

select 
  c1.name
  ,r1.parent 
from 
  cmdb_ci c1 
left join 
  cmdb_rel_ci r1 
on 
  r1.child = c1.name 
left join 
  cmdb_ci_business_app b1 
on 
  b1.name = r1.parent 
where 
  c1.u_core_ci = 'true' 
  and c1.sys_class_name='Business Service' 
    or c1.sys_class_name='Business Application';

[Robert Campbell]

I've updated but it still doesn't work. 

How do you use the OR in the WHERE clause?

[Robert Campbell]

Because of the amount of data, I tried this on a PDI.  I added the u_core_ci true/false column to the cmdb_ci_service table (and extended tables).

Now I have 10 services, 1 cmdb_ci_service_calculated and 9 cmdb_ci_service with u_core_ci = true.

I created one relationship between cmdb_ci_service_calculated and cmdb_ci_business_app and then created this view:

1. 
   1. 
2. 
   1. 
3. 
   1. 
4. 
   1. 
   2. What didn't show here is the one cmdb_ci_service that didn't have any parent at all.
5. 
   
   1. 
6. 
   1. 
7. 
   1. 
8. 
   1. 

I expected one of the results to show 10 records with only one of them showing a parent.