SQL AND on column from join table

Question

I'm having trouble getting the correct results in my query. I'm using Mysql and here is what I have so far:

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, there are multiple rows of the same vendor name in the vendors table. Thus in the result set I would get parts that have two vendor1's instead of a vendor1 and a vendor2. I'm trying to get parts that have at least one of both vendor1 and vendor2.

Any ideas?

Answer 1

The best solution here is to clean up your data in the vendor table. I know this probably isn't possible, but it really is the best solution to consolidate duplicate rows. It will make your life easier in the long run. This also means you need to clean up the code that populates the vendor table, to make sure it doesn't enter duplicates.

In order to figure out what's going wrong in your query, simplify it. Figure out your select statement for the vendor table so that only the vendors you're interested in are returned, and then use it in the inner join above.

select vendor.id, vendor.name, vendor.abbreviated_name from vendor where (concat(IFNULL(vendors.name,''),IFNULL(vendors.abbreviated_name,'')) LIKE '%vendor1%' OR CONCAT(IFNULL(vendors.name,''),IFNULL(vendors.abbreviated_name,'')) LIKE '%vendor2%')

use a LIMIT command if you want to just get the first vendor from the resultset. Put an ORDER BY to make sure the one you want is the first one returned.

Or you can use a GROUP BY to group duplicate vendors. Check out the coalesce command for dealing with null column values and the like.

Answer 2

The problem boils down to the SELECT matching two vendor1's before it finds the first vendor2.

Try using a 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 3

Hi,

As already stated you will want to clear out the duplicates in the vendors table and remap all vendor parts for a given vendor to a distinct vendor id. I am not sure there is really a clear solution to your problem. Initially i thought you might be able to just create a derived table for vendors that only shows a distinct set of vendors and join that to vendor_parts like in the following example:

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

The problem with this approach is that you might have vendor parts tied to two vendors with the same name and different id's which if a group by is used to create a derived table that table will end up loosing some vendor parts when you join it to vendor_parts.

I would personally focus my time on writing an update script to clean up the data instead of trying to find some hack to solve the issue.

Enjoy!

Answer 4

I may be off the mark, 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