Downgrade to non-sub query, but how to change this mysql statement?? 1

[RaffiqEddy]

Dear MySql Expert,

I've to downgrade my mysql statement from sub query(mysql 4x) --to-> non-sub query(mysql 3.32)

I manage to changed below sub-query --> non subquery...

Sub-query
=========

$qsLeaveSUM = "SELECT *
               FROM LeaveSum
               WHERE (nEmpNo = $EmpNo) AND
                     (tYear In 
                        (SELECT MAX(tYear)
                         FROM LeaveSum
                         WHERE (nEmpNo = $EmpNo)))";

Non Sub-query
=============

$qsLeaveSumMaxYear = "SELECT MAX(tYear) as tYear
                      FROM LeaveSum
                      WHERE nEmpNo = $EmpNo";

$rsLeaveSumMaxYear = mysql_query($qsLeaveSumMaxYear) or die("Invalid query");
$rowLeaveSumMaxYear = mysql_fetch_array($rsLeaveSumMaxYear);

$qsLeaveSUM = "SELECT LeaveSum.*
	       FROM LeaveSum
	       WHERE (nEmpNo = $EmpNo) AND
                     (tYear = '$rowLeaveSumMaxYear[tYear]')";

$rsLeaveSUM = mysql_query($qsLeaveSUM) or die("Invalid query");

BUT, how to convert this subquery??

SELECT nEmpNo,
       tYear,
       SUM(nReplacement) AS nReplacementEarn
FROM (
      SELECT ReplacemntDetail.nEmpNo,
             ReplacemntDetail.tYear,
             nTotalDay AS nReplacement
      FROM ReplacemntDetail
      WHERE ReplacemntDetail.nEmpNo=$EmpNo AND
            ReplacemntDetail.tYear='$txtYear' AND
            ReplacemntDetail.nLeaveStatusId=2)
vReplacemntDetail
GROUP BY nEmpNo,
         tYear

Any idea??

TIA

Regards.

 

[r937]

select nEmpNo
     , '$txtYear' as tYear
     , sum(nTotalDay) as nReplacementEarn
  from ReplacemntDetail
 where nEmpNo=$EmpNo 
   and tYear='$txtYear' 
   and nLeaveStatusId=2
group 
    by nEmpNo

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[RaffiqEddy]

r937,

Thank you for the solution, it help a lot!

Can I ask another question about the same subject??

How to convert this sub query??

This query has 2 processes:-
1) Categories “nTotalDay” with a new field name, based on “nLeaveTypeNo”
2) SUM the new fields

SELECT nEmpNo,
       tYear,
       SUM(nAnnualTaken)  AS nAnnualTaken, 
       SUM(nMedicalTaken) AS nMedicalTaken,
       SUM(nUnpaid)       AS nUnpaid,
       SUM(nCarryFwd)     AS nCarryFwd, 
       SUM(nReplacement)  AS nReplacementTaken
FROM (
      SELECT LeaveDetail.nEmpNo,
             LeaveDetail.tYear,
             If(nLeaveTypeNo = 1, nTotalDay, 0)  AS nAnnualTaken,
             If(nLeaveTypeNo = 4, nTotalDay, 0)  AS nMedicalTaken,
             If(nLeaveTypeNo = 2, nTotalDay, 0)  AS nUnpaid,
             If(nLeaveTypeNo = 9, nTotalDay, 0)  AS nCarryFwd,
             If(nLeaveTypeNo = 10, nTotalDay, 0) AS nReplacement
      FROM LeaveDetail
      WHERE LeaveDetail.nEmpNo=$EmpNo AND
            LeaveDetail.tYear='$txtYear' AND
            LeaveDetail.nLeaveStatusId=2)
vLeaveDetail
GROUP BY nEmpNo,
         tYear

TIA

Regards.

 

[r937]

select nEmpNo
     , '$txtYear' as tYear
     , sum(case when nLeaveTypeNo = 1
                then nTotalDay else 0 
             end) as nAnnualTaken
     , sum(case when nLeaveTypeNo = 4
                then nTotalDay else 0 
             end) as nMedicalTaken
     , sum(case when nLeaveTypeNo = 2
                then nTotalDay else 0 
             end) as nUnpaid
     , sum(case when nLeaveTypeNo = 9
                then nTotalDay else 0 
             end) as nCarryFwd
     , sum(case when nLeaveTypeNo = 10
                then nTotalDay else 0 
             end) as nReplacementTaken
  from LeaveDetail
 where nEmpNo = $EmpNo 
   and tYear = '$txtYear' 
   and nLeaveStatusId = 2
group 
    by nEmpNo

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[RaffiqEddy]

r937,

Again, thank you for the solution...

Another star for you!

Take care,

Regards.