SELECT DISTINCT m.manufacturerName cross_brand, ao.oemSearch, ao.oemnumber, ps.id FROM article a /*LEFT JOIN supplier s ON s.supplierId=a.supplierId*/ LEFT JOIN article_oem ao ON ao.articleNumber=a.articleNumber AND ao.supplierId=a.supplierId LEFT JOIN manufacturer m ON m.manufacturerId=ao.manufacturerId LEFT JOIN products.brands b ON b.title=m.manufacturerName LEFT JOIN products.products p ON p.article=ao.oemSearch AND p.brand_id=b.id LEFT JOIN products.products_suppliers ps ON p.id=ps.product_id AND ps.supplier_id IN (223) WHERE ( a.articleSearch IN ('630801', '630802', '522421', '522422', '522431', '522432', '522441', '522442', '522971', '522972', '529971', '624101', '624102', '629981', '629982', '630001', '630002', '630011', '630012', '630021', '630022', '630511', '630512', '63624', '63649') AND a.supplierId=206) OR ( a.articleSearch IN ('D41581C', 'D41589C', 'D41696C', 'D4608', 'D4609', 'D4616', 'D41709C', 'D42704C', 'D4927', 'D7026C') AND a.supplierId=351) OR ( a.articleSearch IN ('FKT021', 'FKT027', 'FKT045', 'F61230', 'F61238', 'F61239', 'F61168', 'F61169', 'F61170', 'F61171', 'F61178', 'F61179', 'F61180', 'F61181', 'F61202', 'F61231', 'F61232', 'F61233', 'F61234', 'F61235', 'F61236', 'F61237', 'F61298', 'F61299', 'F61300', 'F61301') AND a.supplierId=65) OR ( a.articleSearch IN ('400692', '400703', '400704', '401572', '401659', '401735', '401022', '401791', '402669', '410059') AND a.supplierId=4512) OR ( a.articleSearch IN ('FT32431', 'FT32470', 'FT32174', 'FT32175', 'FT32176', 'FT32177', 'FT32477') AND a.supplierId=4938) OR ( a.articleSearch IN ('244009', '244905', '246009', '246907', '248056', '248969', '250004', '250902', '818001') AND a.supplierId=380) OR ( a.articleSearch IN ('2119143', '2119144', '2119145', '2119146', '2119147', '2119148', '2119149', '2119150', '2119151', '2119152', '2137101', '2137102', '2137103', '2137104', '2137107', '2137108', '2137109', '2137110', '2137129') AND a.supplierId=127) OR ( a.articleSearch IN ('HZPFT006A', 'HZPCT006', 'HZPCT007', 'HZPFT002', 'HZPFT003', 'HZTFT002', 'HZTFT003', 'HZPFT006', 'HZPFT007', 'HZPFT008', 'HZPFT009') AND a.supplierId=6355) OR ( a.articleSearch IN ('38454', '38455', '41612', '41613') AND a.supplierId=4683) OR ( a.articleSearch IN ('PRK243', 'PRK244', 'PBRC1042', 'PBRC1043') AND a.supplierId=4558) OR ( a.articleSearch IN ('0590439SX', '0590442SX', '0590450SX', '0498700SX', '0499044SX', '0590530SX', '0590531SX', '0590532SX', '0590533SX', '0590534SX', '60209928KSX', '60209928SX') AND a.supplierId=4595) OR ( a.articleSearch IN ('BHS1307E', 'BHS1308E', 'BHV648E', 'BHV649E', 'BHS1064E', 'BHS1065E', 'BHS1242E', 'BHS1243E', 'BHS1393E', 'BHS1394E', 'BHV578E', 'BHV579E', 'BHV673E', 'BHV674E', 'BHV703E', 'BHV704E', 'BHV705', 'BHV705E') AND a.supplierId=4501) OR ( a.articleSearch IN ('BCA1211', 'BCA1212', 'LCA303', 'LCA335', 'LCA366', 'LCA427', 'LCA534', 'LCA552', 'LCA553', 'LCA588', 'LCA589', 'LCA758', 'LCA844', 'RCA335', 'RCA366', 'RCA427', 'RCA534', 'RCA552', 'RCA553', 'RCA588', 'RCA589', 'RCA758', 'RCA844') AND a.supplierId=4537) OR ( a.articleSearch IN ('99369', '99832', '99866', '99928', '99970') AND a.supplierId=4576) OR ( a.articleSearch IN ('PAL41018', 'PAL41023', 'PAL41025', 'PAL41029', 'PAL41072', 'PAL41099', 'PAL41114', 'PAL41146', 'PAL41223', 'PAL42131', 'PAL42132', 'PAL42133', 'PAL42571', 'PAL42572', 'PAL42575', 'PAL42576', 'PAL42577', 'PAL42773', 'PAL42975', 'PAL42976', 'T45665NEW') AND a.supplierId=442) OR ( a.articleSearch IN ('CTBCJUMIIIFL', 'CTBCJUMIIIFR', 'PGBCBOXIIIFL', 'PGBCBOXIIIFR', 'PGBCBOXIIIRL', 'PGBCBOXIIIRR') AND a.supplierId=4631) OR ( a.articleSearch IN ('24348117695', '24348117705', '24348117715', '24348117725', '24348117915', '24348117955', '24348117965', '24348118115', '24348118125', '24352117115', '24352117125', '24352117135', '24352117145') AND a.supplierId=3) OR ( a.articleSearch IN ('3012663', '45039', '45040', '45043', '45044', '45079', '45080', '45087', '45088') AND a.supplierId=4306) OR ( a.articleSearch IN ('290CA00187', '290CA00188', '290CA00189', '290CA00190', '290CA00259', '290CA00260', '290CA00369', '290CA00370', '290CA00371', '290CA00372', '290CA00375', '290CA00376', '290CA00409', '290CA00410', '290CA01652', '290CA02037', '290CA02038') AND a.supplierId=6271) OR ( a.articleSearch IN ('591974', '591975', '592214', '592215', '592260', '592588', '592589', '592594', '592595', '593294', '593295', '593296', '593297', '593298', '593299', '593300', '593301', '593302', '593303', '594492', '594493', '594518', '594519') AND a.supplierId=49) OR ( a.articleSearch IN ('0204004330', '0204102989', '0204102990', '0204102991', '0204102992', '0204104298', '0204902000', '0204902001') AND a.supplierId=30) OR ( a.articleSearch IN ('AG0007', 'AG0045', 'AG0046', 'AG0047', 'AG0048', 'AG1357', 'AG1358', 'AG1359', 'AG1360', 'AG1361', 'AG1362', 'AG1363', 'AG1364', 'AG1365', 'AG1366', 'AG1830') AND a.supplierId=4993) OR ( a.articleSearch IN ('343190', '343191', '343192', '343193', '343218', '343219', '343220', '343221', '343576', '343910', '343911', '343912', '343913', '343914', '343915', '343916', '343917', '343918', '343919', '344978', '344979', '344980', '344981') AND a.supplierId=114) OR ( a.articleSearch IN ('MD2048', 'MD2049', 'MD2050', 'MD2051', 'MD2062', 'MD2063', 'MD2168', 'MD2169') AND a.supplierId=4604) OR (a.articleSearch='758828' AND a.supplierId=405) OR ( a.articleSearch IN ('CBC141L', 'CBC141R', 'CBC169L', 'CBC169R', 'CBC210L', 'CBC210R', 'CBC214L', 'CBC266L', 'CBC266R', 'CBC286L', 'CBC286R', 'CBC292L', 'CBC292R', 'CBC299L', 'CBC299R', 'CBC482R', 'CBC491L', 'CBC491R') AND a.supplierId=421) OR ( a.articleSearch IN ('LC7587', 'LC7588') AND a.supplierId=89) OR ( a.articleSearch IN ('B190246L', 'B190246R', 'B190247L', 'B190247R') AND a.supplierId=186) OR ( a.articleSearch IN ('3128110', '3135410', '3139610', '3154410', '3157410', '3172020', '3194110', '3195110', '3196110', '3197110', '3228110', '3235410', '3254410', '3257410', '3272020', '3294110', '3295110', '3296110', '3297110', '4170420', '4198110', '4270420', '4298110') AND a.supplierId=460) OR ( a.articleSearch IN ('2274025', '2274026', '2274027', '2274028', '2274087', '2275051', '2275052', '2275081', '2275082', '2284045', '2284046', '2284047', '2284048', '2284093', '2284094', '2284095', '2284096', '2284097', '2284098', '2284117', '2284118', '2284275', '2284276', '2675038') AND a.supplierId=391) OR ( a.articleSearch IN ('DC73918', 'DC73919', 'DC74980', 'DC74981', 'DC83190', 'DC83191', 'DC83192', 'DC83193', 'DC83218', 'DC83219', 'DC83220', 'DC83221', 'DC83576', 'DC83910', 'DC83911', 'DC83912', 'DC83913', 'DC83914', 'DC83915', 'DC83916', 'DC83917', 'RAB84020', 'RAB84029', 'RAB94021', 'RAB94022', 'RAB94023', 'RAB94024', 'RAB94025', 'RAB94026', 'RAB94201', 'RAB94202', 'RAB94203', 'RAB94204', 'RAB94320', 'RAB94329', 'RAB94331', 'RAB94332', 'RAB94687') AND a.supplierId=457) OR ( a.articleSearch IN ('821218', '821345', '821369', '821544', '821547', '821914', '821915', '821916', '821917', '822702', '831218', '831345', '831544', '831547', '831914', '831915', '831916', '831917', '832702', '861918', '862740', '871918', '872740') AND a.supplierId=226) OR ( a.articleSearch IN ('BC53918', 'BC53919', 'BC54980', 'BC54981', 'BC63190', 'BC63191', 'BC63192', 'BC63193', 'BC63576', 'BC63910', 'BC63911') AND a.supplierId=224) OR ( a.articleSearch IN ('23SKV401', '23SKV402', '23SKV403', '23SKV403BLACK', '23SKV403BLUE', '23SKV403RED', '23SKV403YELLOW', '23SKV404', '23SKV404BLACK', '23SKV404BLUE', '23SKV404RED', '23SKV404YELLOW', '23SKV405', '23SKV405BLACK', '23SKV405BLUE', '23SKV405RED', '23SKV405YELLOW', '23SKV406', '23SKV406BLACK', '23SKV406BLUE', '23SKV406RED', '23SKV406YELLOW', '34SKV541', '34SKV542', '42SKV331', '42SKV332', '42SKV841', '42SKV842', '42SKV861', '42SKV862', '42SKV871', '42SKV872', '44SKV001', '46SKV531', '46SKV532', '50SKV083', '50SKV084') AND a.supplierId=4969) OR ( a.articleSearch IN ('53012119143', '53012119144', '53012119145', '53012119146', '53012119147', '53012119148', '53012119149', '53012119150', '53012119151', '53012119152', '53012137101', '53012137102', '53012137103', '53012137104', '53012137107', '53012137108', '53012137109', '53012137110', '53012137129') AND a.supplierId=4770) OR (a.articleSearch='2575BOXIIIF' AND a.supplierId=4674) OR ( a.articleSearch IN ('CTC4403', 'CTC4404') AND a.supplierId=271) OR ( a.articleSearch IN ('FCL694325', 'FCL694369', 'FCL694370', 'FCL694374', 'FCL694375', 'FCL694635', 'FCL694636', 'FCL694775', 'FCL694776', 'FCL694777', 'FCL694778', 'FCL694817', 'FCL694818', 'FCL695091', 'FCL695092', 'FCL695117', 'FCL695118', 'FCL695609', 'FCL695610') AND a.supplierId=62) OR ( a.articleSearch IN ('705113', '705114', '705115', '705116') AND a.supplierId=87) OR ( a.articleSearch IN ('RX449801A0', 'RX449802A0', 'RX449803A0', 'RX449804A0', 'RX449825A0', 'RX449826A0', 'RX4898103A0', 'RX4898135A0', 'RX4898136A0', 'RX489839A0', 'RX489840A0', 'RX489849A0', 'RX489850A0', 'RX529817A0', 'RX529818A0', 'RX529825A0', 'RX529826A0', 'RX529831A0', 'RX529832A0', 'RX529843A0', 'RX529844A0') AND a.supplierId=54) OR ( a.articleSearch IN ('CI6157', 'CI6189', 'CI6190', 'CI6191', 'CI6192', 'CI6193', 'CI6194', 'CI6195', 'CI6196', 'CI6197', 'CI6198', 'FI6215', 'PE6185', 'PE6186', 'PE6193', 'PE6194') AND a.supplierId=316) OR ( a.articleSearch IN ('8AC355385891', '8AC355385901', '8AC355385911', '8AC355385921', '8AC355387131', '8AC355387431', '8AC355387441', '8AC355387671', '8AC355387681', '8AC355387771', '8AC355387781', '8AC355394151', '8AC355394161', '8AC355394231', '8AC355394241', '8AC355395431', '8AC355395441', '8AC355395691', '8AC355395701') AND a.supplierId=4734) OR (a.articleSearch='J3212122' AND a.supplierId=72) OR (a.articleSearch='70617006' AND a.supplierId=6359) OR (a.articleSearch='1361951010' AND a.supplierId=268) OR ( a.articleSearch IN ('JCA1173L', 'JCA1173R', 'JCA1597L', 'JCA1597R') AND a.supplierId=4935) OR ( a.articleSearch IN ('JBC0299', 'JBC0300', 'JBC0301', 'JBC0302', 'JBC0343', 'JBC0344', 'JBC0379', 'JBC0429', 'JBC0430', 'JBC0459', 'JBC0460', 'JBC0461', 'JBC0462', 'JBC0621') AND a.supplierId=185) OR ( a.articleSearch IN ('343190', '343191', '343192', '343193', '343218', '343219', '343220', '343221', '343576', '343910', '343911', '343912', '343913', '343914', '343915', '343916', '343917', '343918', '343919', '344978', '344979', '344980', '344981') AND a.supplierId=286) OR ( a.articleSearch IN ('9914009', '9914010', '9914077', '9914078', '9914509', '9914510', '9918041', '9918042', '9918063', '9918099', '9918100') AND a.supplierId=78) OR (a.articleSearch='6036246' AND a.supplierId=4546) OR ( a.articleSearch IN ('752461', '753438', '753439', '753441', '772460', '772461', '772536', '772537', '772600', '772601', '773222', '773223', '773332', '773434', '773435', '773436', '773437', '773438', '773439', '773440', '773441', '774830', '774831', '775530', '775531', '775532', '775533') AND a.supplierId=4496) OR ( a.articleSearch IN ('BC0262', 'BC0337', 'BC2028', 'BC2029', 'BC2122', 'BC4240', 'BC4246', 'BC6226', 'BC6301') AND a.supplierId=4600) OR ( a.articleSearch IN ('4062', '4063', '4064', '4065', '4066', '4067', '4068', '4069', '4340', '4341') AND a.supplierId=133) OR ( a.articleSearch IN ('270571', '270741', '491956', '820253', '820254', '820257', '820258', '820259', '820260', '820261', '820262', '820325', '820326', '820599', '820600', '820775', '820776', '820777') AND a.supplierId=403) OR ( a.articleSearch IN ('6475004', '6475005') AND a.supplierId=4899) OR ( a.articleSearch IN ('MRP2B03', 'MSE9127') AND a.supplierId=4773) OR (a.articleSearch='43013' AND a.supplierId=4664) OR ( a.articleSearch IN ('6250113', '6250165', '6250166', '6250167', '6250168', '6250315', '6250316', '6250319', '6250320', '6250647', '6250648', '6250663', '6250664', '6251147', '6251148', '6251151', '6251152', '6260315', '6260316') AND a.supplierId=94) OR ( a.articleSearch IN ('VBC240L', 'VBC240R', 'VBC268L', 'VBC268R', 'VBC309L', 'VBC309R', 'VBC313L', 'VBC365L', 'VBC365R', 'VBC385L', 'VBC385R', 'VBC391L', 'VBC391R', 'VBC398L', 'VBC398R', 'VBC581R', 'VBC590L', 'VBC590R') AND a.supplierId=4564) OR (a.articleSearch='N3212100' AND a.supplierId=248) OR (a.articleSearch='T321A125' AND a.supplierId=440) OR ( a.articleSearch IN ('2446448', '2446476', '2446577', '2446613', '2446837', '2447133') AND a.supplierId=4380) OR ( a.articleSearch IN ('BC1057L', 'BC1057R', 'BC1072L', 'BC1072R', 'BC1100L', 'BC1100R', 'BC1135L', 'BC1135R', 'BC1177L', 'BC1177R', 'BC1303L', 'BC1303R', 'BC1356L', 'BC1356R', 'BC1379L', 'BC1607L', 'BC1607R', 'BC1649L', 'BC1649R') AND a.supplierId=129) OR (a.articleSearch='QF00Z00111' AND a.supplierId=4339) OR (a.articleSearch='53TO012' AND a.supplierId=4869) OR ( a.articleSearch IN ('50603426', '50603427', '50603834', '50603835', '50603928', '50604438', '50604439', '50604446', '50604447', '50605648', '50605649', '50605652', '50605653', '50605656', '50605657', '50605660', '50605661', '50605664', '50605665', '50607898', '50607899', '50607988', '50607989') AND a.supplierId=4534) OR ( a.articleSearch IN ('DC73918', 'DC73919', 'DC74980', 'DC74981', 'DC83190', 'DC83191', 'DC83192', 'DC83193', 'DC83218', 'DC83219', 'DC83220', 'DC83221', 'DC83576', 'DC83910', 'DC83911', 'DC83912', 'DC83913', 'DC83914', 'DC83915', 'DC83916', 'DC83917', 'RAB84020', 'RAB84029', 'RAB94021', 'RAB94022', 'RAB94023', 'RAB94024', 'RAB94025', 'RAB94026', 'RAB94201', 'RAB94202', 'RAB94203', 'RAB94204', 'RAB94320', 'RAB94329', 'RAB94331', 'RAB94332', 'RAB94687') AND a.supplierId=214) OR ( a.articleSearch IN ('78B0875', '78B1028', '78B1041') AND a.supplierId=6358) OR ( a.articleSearch IN ('6500014', '6500015', '6500016', '6500017', '6500058', '6500059', '6500062', '6500063', '6500064', '6500065', '6500066', '6500067', '6500074', '6500075') AND a.supplierId=260) OR ( a.articleSearch IN ('13012119143', '13012119144', '13012119145', '13012119146', '13012119147', '13012119148', '13012119149', '13012119150', '13012119151', '13012119152', '13012137101', '13012137102', '13012137103', '13012137104', '13012137107', '13012137108', '13012137109', '13012137110', '13012137129') AND a.supplierId=297) OR ( a.articleSearch IN ('BC11458', 'BC11460', 'BC2001', 'BC2045', 'BC2045R', 'BC21458', 'BC21460', 'BC2401', 'BC2401R', 'BC2402', 'BC2402R', 'BC2403', 'BC2403R', 'BC2404', 'BC2404R', 'BC2405', 'BC2405R', 'BC8720', 'BC8720R', 'BC8989', 'BC8989R', 'BC9120', 'BC9120R', 'BC9928R', 'BC8980', 'BC8980R') AND a.supplierId=4607) OR (a.articleSearch='SKBC0461027' AND a.supplierId=6357) OR (a.articleSearch='T443013' AND a.supplierId=4667) OR ( a.articleSearch IN ('BC0179', 'BC0180', 'BC0363', 'BC0364', 'BC0369', 'BC0370', 'BC0371', 'BC0372', 'BC1806', 'BC1808', 'BC2812', 'BC2814') AND a.supplierId=6312) OR ( a.articleSearch IN ('BR003426', 'BR003427', 'BR003834', 'BR003835', 'BR003928', 'BR004438', 'BR004439', 'BR004446', 'BR004447', 'BR005648', 'BR005649', 'BR005652', 'BR005653', 'BR005656', 'BR005657', 'BR005660', 'BR005661', 'BR005664', 'BR005665', 'BR007898', 'BR007899', 'BR007988', 'BR007989') AND a.supplierId=4830) OR ( a.articleSearch IN ('8170343190', '8170343191', '8170343192', '8170343193', '8170343218', '8170343219', '8170343220', '8170343221', '8170343576', '8170343910', '8170343911', '8170343912', '8170343913', '8170343914', '8170343915', '8170343916', '8170343917', '8170343918', '8170343919', '8170344980', '8170344981') AND a.supplierId=108) OR ( a.articleSearch IN ('21724301', '21724302', '21794303', '21794304', '22534301', '22534302', '23184301', '23184302', '23844301', '23844302', '24254301', '24254302', '24814301', '25714301', '25714302', '25844301', '25844302', '26434302') AND a.supplierId=4739) OR ( a.articleSearch IN ('G01366', 'G01609', 'G02003', 'G02704', 'G02711', 'G02792', 'G03470') AND a.supplierId=6347) OR ( a.articleSearch IN ('BR1139', 'BR1142', 'BR1163', 'BR1166', 'BR1167', 'BR1175', 'BR1198', 'BR1445') AND a.supplierId=4894) OR ( a.articleSearch IN ('230330026', '230430026', '440130033', '440130040', '440130045', '440230033', '440230040', '440230045') AND a.supplierId=86) OR ( a.articleSearch IN ('ZVCER210', 'ZVCER2101') AND a.supplierId=6274)