SELECT distinct(p.id), p.*, b.brand_name,w.part_id as wishlistid, m.model_name, s.status, s.color, ad.color as statuscolor, ad.id as adminstatus_id, ad.adminstatus as adminstatus ,s.id as status_id,t.name as shipment_type,
l.loc_name,h.scaffholder,f.floor,pp.position as part_position,c.name, do.delivery_option as deliveryoption
FROM uphfy_carparts_parts as p
LEFT JOIN uphfy_carparts_brand AS b ON b.id = p.brand_id
LEFT OUTER JOIN uphfy_carparts_carmodels AS m ON p.carmodel_id = m.id
LEFT JOIN uphfy_carparts_status AS s ON p.status_id = s.id
LEFT JOIN uphfy_carparts_floors AS f ON p.floor_id = f.id
LEFT JOIN uphfy_carparts_positions AS pp ON p.position = pp.id
LEFT JOIN uphfy_carparts_scaffholders AS h ON p.scraffholder_id = h.id
LEFT JOIN uphfy_carparts_location AS l ON p.location_id = l.id
LEFT JOIN uphfy_carparts_admin_status AS ad ON p.adminstatus =ad.id
LEFT JOIN uphfy_carparts_shipment_types AS t ON p.shipment_type =t.id
LEFT JOIN uphfy_carparts_categories AS c ON p.cat_id =c.id
LEFT JOIN uphfy_carparts_wishlist AS w ON p.id =w.part_id
LEFT JOIN uphfy_carparts_deliveryoptions AS do ON p.deliveryoption = do.id
LEFT JOIN uphfy_carparts_part_multibrand AS mb ON p.id = mb.part_id
WHERE p.status_id = 1 AND (p.reserved = '' OR p.reserved ='0' OR p.reserved is NULL ) AND (p.brand_id = 21700 OR mb.brand_id =21700) AND p.quantity >0
ORDER BY p.id DESC