Subquery returns more than 1 row sql query

Question

I had such an error can explain what I did wrong?
I only add this sql query:

 (? = (select travel_region_id from relationships where travel_id = travels.id))

error

ActiveRecord::StatementInvalid (Mysql::Error: Subquery returns more than 1 row: select count(*) from travel_start_days, cars, travels
                   where travels.id = travel_start_days.travel_id and travels.id = travel.car_id and travel_start_days.day > adddate(curdate(), interval '2' day) and (7 = (select travel_region_id from relationships where travel_id = travels.id)) and '2013-08-16' <= travel_start_days.day):

Update Whis is method create query

def conditions
      where = ''
      param = []
      if @region && @region != ''
        where += 'travels.region_id = ?'
        param += [@region.to_i]
      end
      if @car && @car != ''
        where += ' and ' if where != ''
        where += 'cars.id = ?'
        param += [@car.to_i]
      end
      if @relation && @relation != '' 
        where += ' and ' if where != ''
        where += '(? = (select travel_region_id from relationships where travel_id = travels.id))'
        param += [@relation.to_i]
      end
      if @start_port && @start_port != ''
        where += ' and ' if where != ''
        where += '(? = (select location_id from travel_days where travel_id = travel_start_days.travel_id and day_no = 1 order by arrival asc limit 1))'
        param += [@start_port.to_i]
      end
      return where == '' ? nil : ["travel_start_days.day > adddate(curdate(), interval ? day) and " + where] + [@criteria[5]] + param
    end

sample data and table structure pls

Praveen Prasannan
– Praveen Prasannan

2013-08-16 07:38:49 +00:00
Commented Aug 16, 2013 at 7:38 — Praveen Prasannan
– Praveen Prasannan, Commented Aug 16, 2013 at 7:38

valex · Accepted Answer · 2013-08-16 07:42:56Z

1

The issue in this part of conditions:

(7 = (select travel_region_id from relationships where travel_id = travels.id))

Obviously this subquery returns more than one travel_region_id just replace = with IN

(7 IN (select travel_region_id from relationships where travel_id = travels.id))

answered Aug 16, 2013 at 7:42

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Naveen Kumar Alone · Accepted Answer · 2013-08-16 07:41:09Z

0

If you are getting more than 1 row from subquery, then add group by clause to your subquery

SELECT travel_region_id FROM relationships 
    WHERE travel_id = travels.id 
    GROUP BY travel_region_id

answered Aug 16, 2013 at 7:41

Naveen Kumar Alone

7,6985 gold badges40 silver badges59 bronze badges

Collectives™ on Stack Overflow

Subquery returns more than 1 row sql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related