sql server - join confusion for mssql -

so want data table-a if attributes available items. i'm using following criteria filter out records

select * product p inner join attributecodesbyproduct atp on atp.product_id = p.product_id  , ltrim(rtrim(atp.attrval)) 'sil - ghi'  or ltrim(rtrim(atp.attrval)) 'sil - def' or ltrim(rtrim(atp.attrval)) 'sil - abc' or ltrim(rtrim(atp.attrval)) not 'xyz'  p.class = 2 

basically want retrieve products class 2 , check attribute table make sure have attributes such ('sil - ghi', 'sil - def', 'sil - abc') , don't have attribute 'xyz'. i'm not sure if should right join or inner join don't want items attribute. single product, there may many different attributes.

appreciate this.

better way write query

select *    product p         inner join attributecodesbyproduct atp                 on atp.product_id = p.product_id                    , ltrim(rtrim(atp.attrval)) in ( 'sil - ghi','sil - def', 'sil - abc' )   p.class = 2  

or if don't want include product when has atleast 1 'xyz' attribute try this

select *    product p         inner join attributecodesbyproduct atp                 on atp.product_id = p.product_id   p.class = 2         , ltrim(rtrim(atp.attrval)) in ('sil - ghi', 'sil - def', 'sil - abc')         , exists (select 1                       product p1                            inner join attributecodesbyproduct atp1                                    on atp1.product_id = p1.product_id                      p.product_id = p1.product_id                     group  p1.product_id                     having count(case when ltrim(rtrim(atp.attrval)) = 'xyz' 1 end) = 0)