CODEWITHSUNDEEP
mysqlsql
Obi
Obi

Reputation: 89

MySQl Query giving wrong result

Select * from YogaTimeTable;

Delete 
from YogaTimeTable
Where RoomNum IN (select tt.RoomNum
                  from YogaRooms r, 
                       YogaTypes t, 
                       YogaTimeTable tt 
                  where r.RoomNum = tt.roomNum
and ((r.RoomCapacity * t.ClassPrice) - (r.CostPerHour * tt.duration / 60)) < 200);

Select * from YogaTimeTable;

The goal is to delete any classes from the timetable that can make less than $200 profit. To calculate the profitability of each class, multiply the roomcapacity by the classprice and then subtract the cost of the room. To calculate the cost of the room multiply the costperhour by the duration divided by 60. but it isn't giving the right result, can someone tell me where I made my mistake. Thanks. The tables are attached.

enter image description here

Upvotes: 0

Views: 96

Answers (2)

RMathis
RMathis

Reputation: 590

Profitability of all classes...

select ytt.YogaID,
       ytt.Day,
       ytt.StartTime,
       ytt.RoomNum,
       yt.ClassPrice,
       ifnull(ytt.Duration,0) as Duration,
       ifnull(yr.CostPerHour,0) as CostPerHour,
       ifnull(yr.RoomCapacity,0) as RoomCapacity,
       round(  ifnull(yr.RoomCapacity,0)*yt.ClassPrice
               - (ifnull(yr.CostPerHour,0)*ifnull(ytt.Duration,0)/60)
             , 2) as Profitability
  from YogaTypes yt
  left join YogaTimeTable ytt on (ytt.YogaID=yt.YogaID)
  left join YogaRooms yr      on (yr.RoomNum=ytt.RoomNum);
+--------+-----------+-----------+---------+------------+----------+-------------+--------------+---------------+
| YogaID | Day       | StartTime | RoomNum | ClassPrice | Duration | CostPerHour | RoomCapacity | Profitability |
+--------+-----------+-----------+---------+------------+----------+-------------+--------------+---------------+
| DRU    | Wednesday | 10:30:00  |       1 |      18.50 |    60.00 |      100.00 |           20 |        270.00 |
| DRU    | Tuesday   | 17:00:00  |       2 |      18.50 |    90.00 |       50.00 |           10 |        110.00 |
| SUN    | Monday    | 07:30:00  |       3 |      18.00 |    60.00 |      150.00 |           25 |        300.00 |
| HAT    | Tuesday   | 07:30:00  |       4 |      20.00 |    90.00 |       70.00 |           15 |        195.00 |
| HAT    | Monday    | 18:30:00  |       4 |      20.00 |    60.00 |       70.00 |           15 |        230.00 |
| NULL   | NULL      | NULL      |    NULL |      17.00 |     0.00 |        0.00 |            0 |          0.00 |
+--------+-----------+-----------+---------+------------+----------+-------------+--------------+---------------+
6 rows in set (0.00 sec)

The classes with profitability less than desired...

select ytt.YogaID,
       ytt.Day,
       ytt.StartTime,
       ytt.RoomNum
  from YogaTypes yt
  left join YogaTimeTable ytt on (ytt.YogaID=yt.YogaID)
  left join YogaRooms yr on (yr.RoomNum=ytt.RoomNum)
  where ifnull(yr.RoomCapacity,0)*yt.ClassPrice
        - (ifnull(yr.CostPerHour,0)*ifnull(ytt.Duration,0)/60) < 200;
+--------+---------+-----------+---------+
| YogaID | Day     | StartTime | RoomNum |
+--------+---------+-----------+---------+
| DRU    | Tuesday | 17:00:00  |       2 |
| HAT    | Tuesday | 07:30:00  |       4 |
| NULL   | NULL    | NULL      |    NULL |
+--------+---------+-----------+---------+
3 rows in set (0.00 sec)

Now to delete the undesirable sessions...

delete tt.*
  from YogaTimeTable tt,
       (select ytt.YogaID,
               ytt.Day,
               ytt.StartTime,
               ytt.RoomNum
          from YogaTypes yt
          left join YogaTimeTable ytt on (ytt.YogaID=yt.YogaID)
          left join YogaRooms yr on (yr.RoomNum=ytt.RoomNum)
         where ifnull(yr.RoomCapacity,0)*yt.ClassPrice
               - (ifnull(yr.CostPerHour,0)*ifnull(ytt.Duration,0)/60) < 200
       ) as unprof
 where tt.YogaID=unprof.YogaID
   and tt.RoomNum=unprof.RoomNum
   and tt.Day=unprof.Day
   and tt.StartTime=unprof.StartTime;
Query OK, 2 rows affected (0.00 sec)

Upvotes: 1

xQbert
xQbert

Reputation: 35343

To me it looks like you have two problems.

  1. A cross join between t and tt exists and should be resolved.
  2. You're attempting to delete based on an incomplete or partial key of YogaTimeTable. The Unique Key of YogaTimeTable appears to be YogaID, StartTime,Day and RoomNum. I say this because the same yoga type could be in the same room at the same time on a different day, or in the same room on the same day at different start times. Thus I think the unique key for YogaTimeTable is a composite key of those 4 fields. So when deleting you need to use the complete key, not a partial key.

So this would result in. .

DELETE FROM YogaTimeTable
WHERE exists 
(SELECT 1
 FROM YogaRooms r
 INNER JOIN YogaTimeTable tt 
   on r.RoomNum = tt.roomNum 
 INNER JOIN YogaTypes t
   on tt.YogaID = t.YogaID 
 WHERE YogaTimeTable.YogaID = TT.YogaID
   and YogaTimeTable.RoomNum = TT.RoomNum
   and YogaTimeTable.StartTime = TT.StartTime
   and YogaTimeTable.Day = TT.Day
   and ((r.RoomCapacity * t.ClassPrice) - (r.CostPerHour * tt.duration / 60)) < 200);

According to: I can use a correlated subquery to delete I just can't alias the table.... https://bugs.mysql.com/bug.php?id=2920

Upvotes: 1

Related Questions