there are three MySQL data table, table 1: the order table tb_order, table 2: consumer tb_expend water table, table 3: staff table tb_user
As shown in the above table structure, the detailed data sheet download address: https://luckytou.lanzous.com/b01nuw0na
Order table tb_order description: order breakfast breakfast, lunch lunch reservation situation, a value of 1 says it has centre, and a value of zero or no record said not order a meal, said Orderdate is ordered which day breakfast lunch,
Consumption meter tb_expend: expens consumption amount, consumption amount to 5, said with breakfast restaurant consumption, consumption amount to 20 said went to a restaurant with a lunch, expensdate is the date of the consumer,
Consult everybody:
Question 1: how to query all booked a lunch in a week, but didn't go to the restaurant with lunch,
Question 2: how to query the all went to a restaurant with breakfast within one week, but there is no reserve personnel breakfast
Please use the SQL commands and programming language (preferably PHP and JSP) to solve the problem

CodePudding user response:

Question 1: how to query all booked a lunch in a week, but "no" "went to a restaurant with a lunch,
The problem there is a key factor that lunch=1, breakfast=1, likely also expend=5, therefore is not necessarily in the result, because expend=5 corresponding breakfast=1,
Reservation record is a include breakfast and lunch reservation situation, two information
Consumption records contain only one information, a consumer is 5 yuan, instructions to eat breakfast, consumption is 20 yuan, that is to go to eat lunch, so if you go to eat breakfast and lunch, consumption records will have two records, if one day didn't eat breakfast lunch, have no the day of consumption records,
So consumption records or not, either expenses=5, or expenses=20