CODEWITHSUNDEEP
mysqlsql
CuriousX
CuriousX

Reputation: 38

SQL ERROR == 1242 (21000): Subquery returns more than 1 row

I am started to learn SQL and I am receiving an error as Subquery returns more than 1 row. In this query I am trying to create a table which include customer on both orders and customers table. Where am I doing wrong?

 SELECT (SELECT DISTINCT customerNumber FROM orders),
        (SELECT customerName FROM customers WHERE customerNumber = (SELECT DISTINCT customerNumber FROM orders));

Upvotes: 0

Views: 3250

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This is a bit long for a comment.

There are different types of subqueries in SQL. A critical distinction is based on the number of rows:

  • A subquery that returns more than one row (and often more than one column) is a derived table.
  • Some subqueries are special. They return at most one row and typically one column. These are scalar subqueries.

A scalar subquery can be used where a constant expression would be used. For instance, you can use a constant for = or in a select. However, you cannot use a set in those locations.

Your query is confusing these types of subqueries. You haven't specified what you want to do, but the entire query needs to be re-written, presumably using JOIN.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You simple do INNER JOIN:

SELECT DISTINCT o.customerNumber, c.customerName
FROM orders o INNER JOIN
     customers C
     ON C.customerNumber = o.customerNumber;

Customer table contains multiple customers associated with orders, So subquery will return more than one record.

So, here subquery won't work. Do INNER JOIN instead.

Upvotes: 1

Related Questions