CODEWITHSUNDEEP
mysqlsubquery
Peng Xu
Peng Xu

Reputation: 25

Subquery returns more than 1 row MySQL

Hey so there are these two tables Name and Code and I am trying to make a table with all the Names and match each existing code to the names. Some Names do not have codes so it should display as NULL for those ones. Anyways this is my code:

SELECT company.name,

(SELECT companyclassification.code
FROM insure_prod.companyclassification
WHERE  company.OIQ_ID = companyclassification.ussicClassification_StdCompany)
AS USSIC_Code

FROM insure_prod.company

When I try to run this it displays Error Code: 1242 Subquery returns more than 1 row

Thanks in advance

Upvotes: 1

Views: 7738

Answers (2)

Taryn
Taryn

Reputation: 247650

You cannot have a correlated subquery return more than one row. One thing you can do would be to use a LEFT JOIN instead of the correlated subquery:

SELECT c.name,
  cc.code AS USSIC_Code
FROM insure_prod.company c
LEFT JOIN insure_prod.companyclassification cc
  on c.OIQ_ID = cc.ussicClassification_StdCompany;

This will return a null for the rows that do not exist in the insure_prod.companyclassification table and if you have more than one row that matches you will return multiple rows for each name. If you do not want to return multiple rows for each name, then you will need to alter the query to return one code for each name.

If you want to continue to use your correlated subquery, then you will have to alter the query to limit the result to one row for each name:

SELECT c.name,
  (SELECT cc.code
   FROM insure_prod.companyclassification cc
   WHERE  c.OIQ_ID = cc.ussicClassification_StdCompany
   ORDER BY cc.ussicClassification_StdCompany 
   LIMIT 1) AS USSIC_Code
FROM insure_prod.company c

Upvotes: 4

Marc B
Marc B

Reputation: 360572

The select companyclassication.code ... query cannot return more than one row. You're trying to return its results into a field context - a field cannot have more than one value in any given row, and you're trying to stuff in 2 or more result records.

Upvotes: 0

Related Questions