CODEWITHSUNDEEP
mysql
dmikester1
dmikester1

Reputation: 1362

mysql query - if subquery returns more than one row

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".

Upvotes: 0

Views: 2203

Answers (1)

fthiella
fthiella

Reputation: 49049

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 :)

Upvotes: 1

Related Questions