CODEWITHSUNDEEP
mysqlsql
user1679500
user1679500

Reputation:

My sql Error Subquery returns more than 1 row

SELECT * ,
  (SELECT ZoneName 
   FROM zone_message_master 
   WHERE ZoneID = zoneid) 'ZoneName' 
FROM zone_master 
WHERE ZoneID=zoneid;

This is a query running in stored procedure and I am getting Error

Subquery returns more than 1 row

Can any one tell me what I have done wrong here?

Upvotes: 2

Views: 1673

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

What I have done wrong here?

The error, you got, is pretty clear. The subquery must return exactly one row, you can limit the number of rows using ORDER BY ... LIMIT 1.

How can I fix that?

You didn't need a subquery for that, JOIN the two tables directly instead like so:

SELECT z.ZoneId, zm.ZoneName
FROM zone_message_master zm
INNER JOIN zone_master z ON zm.ZoneId = z.ZoneId

Upvotes: 2

WojtusJ
WojtusJ

Reputation: 1318

The problem is probably as clear as the error message you got. You can trick the subquery to return only one row, but this is not the case. You intention as I see is that the table zone_message_master should have unique ZoneID values. To be correct you should create a unique index on zone_message_master.ZoneID column. This will of course require some cleaning in that table, but finally will alow you to be sure it works correct.

Upvotes: 0

alex
alex

Reputation: 3742

That means you have more than one entry in zone_message_master for your zoneid. This query will be more correct:

SELECT zm.* , zmm.ZoneName 
FROM zone_master zm
JOIN zone_message_master zmm ON zm.ZoneID=zmm.ZoneID
WHERE zm.ZoneID=zoneid;

Upvotes: 1

Related Questions