SELECT m.manufacturername cross_brand, ao.oemsearch cross_number FROM tecdoc2021.article a
LEFT JOIN tecdoc2021.article_oem ao ON ao.articleNumber=a.articleNumber AND ao.supplierId=a.supplierId
LEFT JOIN tecdoc2021.manufacturer m ON m.manufacturerId=ao.manufacturerId
WHERE a.articleSearch='HOS3318' AND a.supplierId IN (4537) UNION
SELECT s.supplierName cross_brand, CASE WHEN ar.replacedByArticles IS NULL
THEN ar.replacesarticles
ELSE ar.replacedByArticles
END AS cross_number FROM tecdoc2021.article a
LEFT JOIN tecdoc2021.article_replace ar ON ar.supplierId=a.supplierId AND ar.articlenumber=a.articleNumber
LEFT JOIN tecdoc2021.supplier s ON s.supplierId=ar.supplierid
WHERE a.supplierId IN (4537) AND a.articleSearch='HOS3318'
UNION ALL
SELECT DISTINCT s.supplierName cross_brand, o.articlenumber as cross_number FROM tecdoc2021.article a
LEFT JOIN tecdoc2021.article_oem ao ON ao.articlenumber=a.articlenumber AND ao.supplierid=a.supplierid
LEFT JOIN tecdoc2021.article_oem o ON o.manufacturerid=ao.manufacturerid AND o.oemsearch=ao.oemsearch AND o.articlenumber != a.articlenumber AND o.supplierid != a.supplierid
LEFT JOIN tecdoc2021.supplier s ON s.supplierid=o.supplierid
WHERE a.articlesearch='HOS3318' AND a.supplierid IN (4537)
UNION ALL
SELECT DISTINCT s.supplierName cross_brand, a2.articlenumber as cross_number FROM tecdoc2025.article a
LEFT JOIN tecdoc2025.article_oem ao ON ao.article_id=a.id
LEFT JOIN tecdoc2025.article_oem o ON o.manufacturerid=ao.manufacturerid AND o.oemsearch=ao.oemsearch /*AND o.articlenumber != a.articlenumber AND o.supplierid != a.supplierid*/
LEFT JOIN tecdoc2025.article a2 ON o.article_id=a2.id
LEFT JOIN tecdoc2025.suppliers s ON s.supplierid=a2.supplierid
WHERE a2.id != a.id AND a.articlesearch='HOS3318' AND a.supplierid IN (4537)
UNION
SELECT m.manufacturername as cross_brand, ao.oemsearch as cross_number FROM tecdoc2025.article_oem ao
LEFT JOIN tecdoc2025.manufacturer m ON m.manufacturerid=ao.manufacturerid
WHERE ao.article_id IN ( SELECT id FROM tecdoc2025.article WHERE articlesearch='HOS3318' AND supplierid IN ( 4537 ))
UNION
SELECT m.manufacturername as cross_brand, ao.oemsearch as cross_number FROM tecdoc2021.article_oem ao
LEFT JOIN tecdoc2021.manufacturer m ON m.manufacturerid=ao.manufacturerid
WHERE ao.articlenumber = 'HOS3318' AND ao.supplierid IN ( 4537 )
+7 (909) 160-57-10
