Accessories

SELECT DISTINCT svac.*
							FROM shop_item_has_category
							  INNER JOIN shop_view_active_item ON (
							    shop_view_active_item.company = shop_item_has_category.company AND 
							    shop_view_active_item.shop_code = shop_item_has_category.shop_code AND 
							    shop_view_active_item.language_code = shop_item_has_category.language_code AND 
                                    ( 
                                        shop_item_has_category.item_no = shop_view_active_item.item_no 
                                        OR 
                                        shop_item_has_category.item_no = shop_view_active_item.parent_item_group_no
                                    )
							    )
							  
                                            LEFT JOIN shop_item_attribute_links_ext sial ON (
                                                    sial.company = 'DESOI'
                                                AND	sial.shop_code = 'B2B'
                                                AND sial.no = shop_view_active_item.item_no
                                                AND (sial.language_code = 'ENU' OR sial.language_code = '')
                                            )

                                RIGHT JOIN shop_view_active_item svac 
                                ON svac.item_no = shop_view_active_item.item_no
                                    AND svac.company = shop_view_active_item.company 
                                    AND svac.shop_code = shop_view_active_item.shop_code 
                                    AND svac.language_code = shop_view_active_item.language_code
                                
							  WHERE (shop_item_has_category.category_line_no = 158828)
							  AND shop_view_active_item.language_code = 'ENU'
							  AND shop_view_active_item.company = 'DESOI'
							  AND shop_view_active_item.shop_code = 'B2B'
							  AND shop_item_has_category.company = 'DESOI'
							  AND shop_item_has_category.shop_code = 'B2B'
							  AND shop_item_has_category.language_code = 'ENU'
							  AND shop_item_has_category.category_shop_code = 'B2B'
							 
                                            AND
                                                    ( ( sial.attribute_code='ZUB-IGMG' AND sial.value_option = 'geka-kupplung'))
                                                AND sial.shop_code = 'B2B'
                                                AND (
                                                        (
                                                            sial.type = 0
                                                        AND sial.company = 'DESOI'
                                                        )
                                                    OR (
                                                            sial.company IS NULL
                                                        AND sial.type IS NULL
                                                        )
                                                ) GROUP BY shop_view_active_item.item_no  HAVING COUNT(DISTINCT sial.attribute_code) = 1