mysql query - if subquery returns more than one row

Question

I have a super long complicated mysql query that I could paste here, but I don't think it would help with my question. Basically, I have a subquery that sometimes returns more than one row. When that happens the whole query bombs out. So what I would like to do is have the subquery return it's normal result for that column unless more than one row is returned, then output some specified text in that column. Make sense?

Edit: as requested, I will post my large query. I may be doing it the hard way, but it took me forever to get the query to this point, so I wouldn't know where to begin rewriting it.

SELECT TIME_FORMAT(
       TIMEDIFF(
         (SELECT PunchDateTime
         FROM timeclock_punchlog tp
         WHERE PunchEvent = 'breakin'
         AND DATE(tp.PunchDateTime) = DATE(U.time)
         AND tp.EmpID = U.EmpID),
         (SELECT PunchDateTime
         FROM timeclock_punchlog tp
         WHERE PunchEvent = 'breakout'
         AND DATE(tp.PunchDateTime) = DATE(U.time)
         AND tp.EmpID = U.EmpID)), '%i min.') AS Lunch
       FROM ((SELECT `enter`.EmpID,
       `enter`.PunchDateTime AS `time`,
       DATE_FORMAT(`enter`.PunchDateTime, '%m-%d-%Y')
       AS 'Punch Date',
       TIMESTAMPDIFF(SECOND, `enter`.PunchDateTime, '2003-05-01 00:00:00')
       AS `delta`
       FROM timeclock_punchlog AS `enter`
       WHERE `enter`.`In-Out` = 1)
       UNION
       (SELECT `leave`.EmpID,
       `leave`.PunchDateTime AS `time`,
       DATE_FORMAT(`leave`.PunchDateTime, '%m-%d-%Y')
       AS 'Punch Date',
       -TIMESTAMPDIFF(SECOND, `leave`.PunchDateTime, '2003-05-01 00:00:00')
       AS `delta`
       FROM timeclock_punchlog AS `leave`
       WHERE `leave`.`In-Out` = 0)) AS U
               LEFT JOIN prempl pe ON u.EmpID = pe.prempl
      WHERE DATE(U.`time`) >= ?startDate
      AND DATE(U.`time`) < ?endDate
    GROUP BY date(U.`time`), EmpID
  ORDER BY U.EmpID, U.`time` ASC";

The part that is sometimes returning more than one row is the query defined as "Lunch".

This could be done, but the problem you describe suggests that something is wrong in your query logic. Please post a simplified version of your query as well as a short description of what the query is returns. And the workaround you are looking for would look so ugly that I would not dare to say it :) — RandomSeed
– RandomSeed, Commented Nov 14, 2013 at 20:25
So, basically, the problem arises when there are more than one "breakin" or "breakout" event in one given day. Is this normal situation? If not, perhaps you had better cleanning your data and preventing the data from being inserted in the first place. If it is, don't you want to sum the break times instead? — RandomSeed
– RandomSeed, Commented Nov 14, 2013 at 21:10
More than one break can occur, so I do need to account for it. I guess I could sum it up, but I would prefer just to spit out "multiple breaks" so the accountant can go in and look at the specific user to determine what needs to be done. — dmikester1
– dmikester1, Commented Nov 14, 2013 at 21:15

fthiella · Accepted Answer · 2013-11-14 20:27:18Z

1

If your query is similar to this:

SELECT id, col1, col2, (SELECT somecol FROM yourtable WHERE ...)
FROM   anothertable
....

you could, for example, use some tricks like this:

SELECT id, col1, col2,
       CASE WHEN
         (SELECT COUNT(*) FROM (..your subquery..) AS s)>1 THEN 'specified text'
       ELSE
         (..your subquery..)
       END AS value_returned_by_your_subquery
FROM   anothertable
....

Please see an example here.

But when a query begins to get a little bit too complicated, or when it requires some dirty tricks like this, it is usually better to think again if you really need to do it this way, maybe there's a better and cleaner way, but without seeing the actual query I am not able to help you more :)

answered Nov 14, 2013 at 20:27

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

1 Comment

dmikester1 Over a year ago

It took me a while, but I was able to make this work. Thank you

Collectives™ on Stack Overflow

mysql query - if subquery returns more than one row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related