0

I get the error "Subquery returns more than 1 row" when I run the following statement.

SELECT `labels`.*, 
       IF((SELECT `label_id` 
           FROM   `customer_print_sessions` 
           WHERE  `customer_id` = (SELECT `customer_id` 
                                   FROM   `customer_accounts_logins` 
                                   WHERE  `id` = '1069') 
                  AND `customer_print_sessions`.`label_id` = `labels`.`id`) IS 
          NOT NULL 
       , 'Y', 'N') `in_queue` 
FROM   `labels` 
WHERE  `id` IN (SELECT `label_id` 
                FROM   `customer_groups_label_relation` 
                WHERE  `group_id` IN (SELECT `group_id` 
                                      FROM   `customer_accounts_group_relation` 
                                      WHERE  `customer_id` = 
                                             (SELECT 
                                             `customer_id` 
                                                              FROM 
                                             `customer_accounts_logins` 
                                                              WHERE  `id` = 
                                             '1069' 
                                             ))) 
ORDER  BY `labels`.`plu` ASC; 

I ran each SELECT statement separately to determine which one returns multiple results that this statement as a whole was not expecting. I've determined that the following part of the above statement is the part that is returning multiple results which aren't expected.

SELECT `label_id`
FROM   `customer_print_sessions`
WHERE  `customer_id` = (SELECT `customer_id`
                        FROM   `customer_accounts_logins`
                        WHERE  `id` = '1069')
       AND `customer_print_sessions`.`label_id` = `labels`.`id` 

How can I write this so that the IF function and the whole statement will work with the multiple results that are being returned?

UPDATE: Since the above statement returns multiple results of the same value, is there a way I can write it to just return one?

1

I added GROUP BY label_id to make the original statement work.

SELECT `labels`.*, 
       IF((SELECT `label_id` 
           FROM   `customer_print_sessions` 
           WHERE  `customer_id` = (SELECT `customer_id` 
                                   FROM   `customer_accounts_logins` 
                                   WHERE  `id` = '1069') 
                  AND `customer_print_sessions`.`label_id` = `labels`.`id` GROUP BY `label_id`) IS 
          NOT NULL 
       , 'Y', 'N') `in_queue` 
FROM   `labels` 
WHERE  `id` IN (SELECT `label_id` 
                FROM   `customer_groups_label_relation` 
                WHERE  `group_id` IN (SELECT `group_id` 
                                      FROM   `customer_accounts_group_relation` 
                                      WHERE  `customer_id` = 
                                             (SELECT 
                                             `customer_id` 
                                                              FROM 
                                             `customer_accounts_logins` 
                                                              WHERE  `id` = 
                                             '1069' 
                                             ))) 
ORDER  BY `labels`.`plu` ASC; 
0

Off the cuff suggestion (I don't have a database server up at the moment to test, sorry):

(SELECT COUNT(*)
       FROM   `customer_print_sessions` 
       WHERE  `customer_id` = (SELECT `customer_id` 
                               FROM   `customer_accounts_logins` 
                               WHERE  `id` = '1069') 
              AND `customer_print_sessions`.`label_id` = `labels`.`id`) IS 
      NOT NULL 
-- or
EXISTS (SELECT 1
       FROM   `customer_print_sessions` 
       WHERE  `customer_id` = (SELECT `customer_id` 
                               FROM   `customer_accounts_logins` 
                               WHERE  `id` = '1069') 
              AND `customer_print_sessions`.`label_id` = `labels`.`id`)

Subqueries used as expressions must return at most one row and one column.

  • Could you please provide more explanation with this answer? Thank you. – tacudtap Mar 6 '14 at 19:44
0

It would seem that the query you are sending is returning more than one identical row. Where this is a problem is if you were to go on and do something like a delete or update as the server would not know which row needed to be updated - so it doesn't like it.

A Count(*) option will tell you how many records there are - if you are expecting 10 rows and there are thirty there is a problem - and the select 1 will return the first of the rows that it matches. You could also try a SELECT TOP 1 or SELECT LIMIT 1

I would be tempted to look at a DISTINCT statement which should only return the rows that are not duplicated, each row would be distinct. e.g. SELECT DISTINCT label_id

I would also try and avoid the use of * in the select. This will return every column and you have no control as to how that can affect the rest of the statement. Try selecting only the columns you need. Also, if you only need one result, is there a distinct column you can use such as an ID column?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.