question

obadaahmar avatar image
obadaahmar asked

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. [Tables][1] [1]: https://i.stack.imgur.com/Q4Q3n.jpg
sqlmysql
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you need extra parenthesis around r.CostPerHour * tt.duration
0 Likes 0 ·
@Rob 5 No, OP does not need to add any more parenthesis, the syntax is correct but there is a bad flaw in the logic because the condition to join yoga types and yoga time table records is missing which produces Cartesian product between the 2 rendering the delete statement incorrect.
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
If the tables were SQL Server database tables then the statement would look like this: delete tt from YogaTimeTable tt inner join YogaRooms r on r.RoomNum = tt.RoomNum inner join YogaTypes t on t.YogaID = tt.YogaID where (r.RoomCapacity * t.ClassPrice) - (r.CostPerHour * tt.duration / 60) < 200 Given that the tables are MySQL tables and preserving the old style syntax, you can leave the query as it is and just replace the line reading where r.RoomNum = tt.roomNum with the line reading where r.RoomNum = tt.roomNum and t.YogaID = tt.YogaID This should do it. Hope this helps. Oleg
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.