SQL AND by column from join table

Question

I am having problems getting the correct results in my query. I am using Mysql, and here is what I still have:

SELECT cpn, status, title, value_category, rating_category, parts.id 
FROM `vendors` 
INNER JOIN `vendor_parts` ON (`vendors`.`id` = `vendor_parts`.`vendor_id`) 
INNER JOIN `parts` ON (`parts`.`id` = `vendor_parts`.`part_id`) 
WHERE (concat(IFNULL(vendors.name,''),IFNULL(vendors.abbreviated_name,'')) 
  LIKE '%vendor1%'
OR 
CONCAT(IFNULL(vendors.name,''),IFNULL(vendors.abbreviated_name,'')) 
  LIKE '%vendor2%') 
GROUP BY cpn 
HAVING COUNT(cpn)=2 
ORDER BY cpn

The problem is that for some rows in the parts table in the suppliers table there are several rows of the same supplier name. Thus, in the result set, I would get parts in which there are two suppliers1, and not supplier1 and supplier2. I am trying to get parts that have at least one of them: vendor1 and vendor2.

Any ideas?

Answer 1

I may not notice, but if you are trying to find parts that are provided by both vender1 and vender2, the best strategy is to use a join

SELECT cpn, status, title, value_category, rating_category, parts.id
from `parts`
inner join 
   (
       select distinct part_id from `vender_parts` join ON (`vendors`.`id` = `vendor_parts`.`vendor_id`) 
      WHERE concat(IFNULL(`vendors`.`name`,''),IFNULL(`vendors`.`abbreviated_name`,'')) LIKE '%vendor1%'
   ) `vender1` on `vender1`.`part_id` = `parts`.`id`
inner join 
   (
       select distinct part_id from `vender_parts` join ON (`vendors`.`id` = `vendor_parts`.`vendor_id`) 
      WHERE concat(IFNULL(`vendors`.`name`,''),IFNULL(`vendors`.`abbreviated_name`,'')) LIKE '%vendor2%'
   ) `vender2` on `vender2`.`part_id` = `parts`.`id`
group by cpn

Answer 2

. , , , , . . , , , , .

, , . select , , .

vendor.id, vendor.name, vendor.abbreviated_name (concat (IFNULL (vendors.name, ''), IFNULL (vendors.abbreviated_name, '')) LIKE '% vendor1%' ​​CONCAT (IFNULL (vendors.name, ''), IFNULL (vendors.abbreviated_name, ' ')) LIKE'% vendor2% ')

LIMIT, . ORDER BY, , , , .

GROUP BY . coalesce ..

Answer 3

SELECT, 1, 2.

UNION:

SELECT cpn, status, title, value_category, rating_category, parts.id  
FROM `vendors`  
INNER JOIN `vendor_parts` ON (`vendors`.`id` = `vendor_parts`.`vendor_id`)  
INNER JOIN `parts` ON (`parts`.`id` = `vendor_parts`.`part_id`)  
WHERE concat(IFNULL(vendors.name,''),IFNULL(vendors.abbreviated_name,''))  
  LIKE '%vendor1%'  
GROUP BY cpn  
HAVING COUNT(cpn)=1
UNION
SELECT cpn, status, title, value_category, rating_category, parts.id  
FROM `vendors`  
INNER JOIN `vendor_parts` ON (`vendors`.`id` = `vendor_parts`.`vendor_id`)  
INNER JOIN `parts` ON (`parts`.`id` = `vendor_parts`.`part_id`)  
WHERE CONCAT(IFNULL(vendors.name,''),IFNULL(vendors.abbreviated_name,''))  
  LIKE '%vendor2%'
GROUP BY cpn  
HAVING COUNT(cpn)=1
ORDER BY cpn

Answer 4

, . , . , , vendor_parts, :

SELECT  
     cpn ,
     status,
     title,
     value_category,
     rating_category,
     parts.id
FROM     
    (select * from vendors where vendors.id in (select id from vendors group by vendors.name)) vendors
    INNER JOIN vendor_parts
    ON       (vendors.id = vendor_parts.vendor_id)
    INNER JOIN parts
    ON       (parts.id = vendor_parts.part_id)
WHERE(
    concat(IFNULL(vendors.name,''),IFNULL(vendors.abbreviated_name,'')) LIKE '%vendor1%'
    OR  CONCAT(IFNULL(vendors.name,''),IFNULL(vendors.abbreviated_name,''))  LIKE '%vendor2%'
    )
GROUP BY cpn
HAVING   COUNT(cpn)=2
ORDER BY cpn

, , , , , vendor_parts.

I personally will focus my time on writing an update script to clear the data, rather than trying to find a hack to solve the problem.

Enjoy it!