CODEWITHSUNDEEP
mysql
ManMetWebTek
ManMetWebTek

Reputation: 11

MySQL query "more than one row"

Hi I have a mysql query below

SELECT `company`,`type`,`code` FROM `location` WHERE (
SELECT code FROM meter WHERE `meter`.`location_code`=location.code AND meter.code NOT IN (
SELECT meter_code FROM reading
))

I receive an error saying that more than one row is returned

Can I ask why/have guidance

Other questions haven't really helped me to be honest

Upvotes: 0

Views: 49

Answers (2)

xQbert
xQbert

Reputation: 35323

Since you don't need data from meter or meter code 

SELECT `company`,`type`,`code` 
FROM `location` 
WHERE EXISTS (SELECT * 
              FROM meter 
              LEFT JOIN Reading R
                on meter.code = R.meter_Code
              WHERE `meter`.`location_code`=location.code 
                and R.meter_Code is null
             )

or to keep with the theme of using exists (this avoids performance impact of distinct and avoids the joins; but a not exists can be slow.

SELECT `company`,`type`,`code` 
FROM location l 
WHERE EXISTS (SELECT * 
              FROM meter m
              WHERE m.location_code=l.code 
                and not exists (SELECT * 
                                FROM READING r
                                WHERE R.meter_Code = m.Code

             )

This is how it could be done with joins, but the distinct and joins seem like they could be costly. distinct is necessary as I assume a location may have many meters or vice versa or a meter may have many readings which cause the data to be multiplied thus distinct; but at a cost.

SELECT DISTINCT l.company,l.type,l.code
FROM location l
INNER JOIN METER M
 on l.code = m.location_Code
LEFT JOIN reading R
 on R.Meter_Code = m.Code
WHERE r.meter_Code is null

Would need to test each to find what performance best suits your need. record count in each table indexes and data distribution could all change the performance of each of these. I'm partial to the last one, then first one from a maintenance standpoint but it could be the worst in performance.

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

I think you need to use the EXISTS operator:

SELECT `company`,`type`,`code` 
FROM `location` 
WHERE EXISTS (SELECT code 
              FROM meter 
              WHERE `meter`.`location_code` = location.code AND 
                     meter.code NOT IN (SELECT meter_code FROM reading))

Upvotes: 0

Related Questions