CODEWITHSUNDEEP
mysql
tseres
tseres

Reputation: 37

How to pass a value to a subquery in MySQL

Im having trouble passing a value to a subquery in MySQL, for what i have read this is not possible in MySQL, but i cant manage to find anotherway to do this query.

the value to pass is "pnl_partsA.part_image" from the first part of the query. What Im tying to obtain is the part_image that is present in more than one manufacturer.

SELECT manufacturer, chapter, part_image
FROM pnl_parts pnl_partsA
WHERE 1 = 1
AND part_image <> ''
#AND manufacturer = 'fiat'
#AND part_image = 'F01A050'
AND (   SELECT COUNT(chapter)
        FROM (  SELECT manufacturer, chapter, part_image
                FROM pnl_parts
                WHERE part_image = pnl_partsA.part_image
                AND part_image <> ''
                AND manufacturer = pnl_partsA.manufacturer
                GROUP BY manufacturer, chapter, part_image
             )  chaptercount 
    )  > 1
ORDER BY part_image
;

Adding more information... What I need to get are the chapters that have a duplicate part_image

enter image description here

any help will be appreciated thanks.

Upvotes: 0

Views: 163

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

You can get all the manufacturer and part_image group(s), which has more than one rows, using Group By and Having COUNT(*) > 1. We will use this result-set in a Derived table, and Join to the main table, to get the relevant row(s):

Try the following query instead: 

SELECT 
  pp1.manufacturer, 
  pp1.chapter, 
  pp1.part_image 
FROM pnl_parts AS pp1
JOIN (
       SELECT 
         pp2.manufacturer, 
         pp2.part_image 
       FROM pnl_parts AS pp2 
       WHERE pp2.part_image <> ''
       GROUP BY 
         pp2.manufacturer, 
         pp2.part_image
       HAVING COUNT(*) > 1
     ) AS dt 
  ON dt.manufacturer = pp1.manufacturer AND 
     dt.part_image = pp1.part_image

Upvotes: 1

Related Questions