Jim Fell Jim Fell - 4 months ago 12
MySQL Question

MySQL How to deal with Error: Subquery returns more than 1 row?

I'm trying to write a query where it selects all records from a table where certain complex/nested criteria are met. The logic in my query I think is correct, but the problem I'm running into is the final subquery (see example) is returning more than 1 row, which is what I would expect/need. So, the problem is, how do I deal with this? Does MySQL support some kind of looping or set criterion?

SELECT c.primary_key
FROM esjp_content c
WHERE template_id = (
SELECT DISTINCT esjp_content.template_id
FROM esjp_content
INNER JOIN esjp_hw_config ON esjp_content.template_id = esjp_hw_config.proc_id
INNER JOIN esjp_assets ON esjp_hw_config.primary_key = esjp_assets.hw_config_id
WHERE
esjp_content.summary_id > 0
AND
(esjp_assets.asset_label='C001498500' OR esjp_assets.asset_label='H0065' OR esjp_assets.asset_label='L0009')
)
AND
EXISTS (SELECT 1 FROM esjp_content c2 WHERE c2.summary_id = c.primary_key)
AND
c.primary_key != (
/* This subquery returns more than 1 result. */
SELECT esjp_signoffs.content_id
FROM esjp_signoffs
INNER JOIN esjp_assets ON esjp_signoffs.asset_id = esjp_assets.primary_key
WHERE
esjp_signoffs.user_id=1
AND
(esjp_assets.asset_label='C001498500' OR esjp_assets.asset_label='H0065' OR esjp_assets.asset_label='L0009')
);


For additional details on my tables, see this other StackOverflow post I made earlier today. (This is an entirely different question.)

Answer

If i understan correctly the

   c.primary_key != (
    /* This subquery returns more than 1 result. */
    SELECT esjp_signoffs.content_id
    FROM esjp_signoffs
    INNER JOIN esjp_assets ON esjp_signoffs.asset_id = esjp_assets.primary_key
    WHERE
        esjp_signoffs.user_id=1
            AND
        (esjp_assets.asset_label='C001498500' OR esjp_assets.asset_label='H0065' OR esjp_assets.asset_label='L0009')
);

return more then a row .. you could use not in ..

  c.primary_key not in (

or

 inner join  (  ..... ) t on c.primary_key != t.the_column_you_need
Comments