problem in where clause to check if the date is exist in another table

[newphpcoder]

Hi..

 

I have this code for checking if the date(TIMEOUT) is already exist in nrs table:

 

      $EMP_NO = $_GET['EMP_NO'];
      $DATE_NRS = $_GET['DATE_NRS'];
      $TIME_IN = strtotime($_GET['TIME_IN']);
      $TIME_OUT = strtotime($_GET['TIME_OUT']);
      $APPROVE = $_GET['APPROVE']; 

$sql = "SELECT EMP_NO, TIME_IN, TIME_OUT, TOTAL_HOURS, NRS_STATUS FROM nrs WHERE EMP_NO = '$EMP_NO' AND DATE(TIME_OUT) = '$TIME_OUT'";  

//echo $sql;
  $RsOtData = $conn2->Execute($sql);
  
  $numrows = $RsOtData->RecordCount();
  
  
  if($numrows > 0){ 
  echo "<script>alert('Transaction cannot be process')</script>";
echo "<script>navigate('NRSEmp.php')</script>";   
  }
  else{
  $saverec['EMP_NO'] = $EMP_NO;
  //$saverec['DATE_NRS'] = $DATE_NRS;
  $saverec['TIME_IN'] = $TIME_IN;
  $saverec['TIME_OUT'] = $TIME_OUT;
  $saverec['TOTAL_HOURS'] = $TOTAL_HOURS;
  $saverec['NRS_STATUS'] = $APPROVE;
  
  $insertSQL = $conn2->GetInsertSQL($RsOtData, $saverec);
  $conn2->Execute($insertSQL); 
  }

 

now I need revise my query to check also if the date from $TIME_OUT is equal to reg_att .

 

for example of reg_att table data:

 

EMP_NO = 00000221

LOGIN = 2012-03-01 05:35:00

LOGOUT = 2012-03-01 13:35:00

if Date from $TIMEOUT = date(LOGOUT) if condition will work.

 

I trid this query

SELECT n.EMP_NO, n.TIME_IN, n.TIME_OUT, TOTAL_HOURS, NRS_STATUS FROM nrs n WHERE EMP_NO = '00000221' AND DATE(TIME_OUT) = '2012-03-01' OR DATE(TIME_OUT) = (SELECT DATE(LOGOUT) FROM reg_att r WHERE r.EMP_NO = n.EMP_NO);

 

and i got an error:

 

Error Code : 1242

Subquery returns more than 1 row

(0 ms taken)

 

 

Thank you

[tipsmail7]

This error caused by the subquery

SELECT DATE(LOGOUT) FROM reg_att r WHERE r.EMP_NO = n.EMP_NO

wasn't returns unique row

 

as suggested in http://stackoverflow.com/questions/2419094/help-with-subquery-returns-more-than-1-row

maybe you could replace '=' with IN

 

SELECT n.EMP_NO, n.TIME_IN, n.TIME_OUT, TOTAL_HOURS, NRS_STATUS FROM nrs n WHERE EMP_NO = '00000221' AND DATE(TIME_OUT) = '2012-03-01' OR DATE(TIME_OUT) IN (SELECT DATE(LOGOUT) FROM reg_att r WHERE r.EMP_NO = n.EMP_NO);