I am trying to create a solution which allows pilots to swap a 1, 2, 3 or 4 day trip for another pilot's 1, 2, 3 or 4 day trip. I have 3 tables, Pilot, Have and Want. A pilot creates a Have which, for example, is a 2 day on 10/20 (October 20th). This pilot wants a 3 day starting on the 22. Another pilot has the 3 day and he wants something like the other pilot's 2 day. The tables looks like this;
我正在尝试创建一个解决方案,允许飞行员在另一个飞行员的1,2,3或4天旅行中换掉1,2,3或4天的旅程。我有3张桌子,Pilot,Have和Want。例如,飞行员会在10月20日(10月20日)创建一个2天。这名飞行员希望在22日开始为期3天。另一名飞行员需要3天时间,他想要像其他飞行员那样的2天。表格看起来像这样;
id_pilot, name, phone, employee_num, aircraft, base, seat
1 Steve 363-0040 123454 320 DCA FO
2 Ted 992-5380 123455 320 DCA FO
id_have, id_pilot, daytrip, start_month, start_day
1 1 02 10 20
2 2 03 10 22
id_want, id_have, daytrip, start_month, start_day
1 1 03 10 22
To see what Wants are out there for a particular Have I need to join the Want and Have table on something that looks like DCA|320|FO|10|20|2. I only want to see the Wants for a particular Have that are for the aircraft, base and seat. I can do this by creating a new join field but having such a simplistic understanding of MySQL I imagine there is a way to do this on the fly. I used joins to grab information via the primary keys but this seems like it's one step removed from that. What would such a query look like?
要了解特定的东西是什么需要加入想要和拥有表格的东西看起来像DCA | 320 | FO | 10 | 20 | 2。我只想看到特定的飞机,底座和座椅的想要。我可以通过创建一个新的连接字段来实现这一点,但对MySQL有这么简单的理解我想象有一种方法可以实现这一点。我使用连接通过主键获取信息,但这似乎是从中删除的一步。这样的查询会是什么样的?
1 个解决方案
#1
2
To give an example of what Marc B is saying, the join clause can compare the daytrip, start_month and start_date columns between the have and want tables, e.g.
举一个Marc B所说的例子,join子句可以比较have和want表之间的daytrip,start_month和start_date列,例如:
select have.id_pilot, want.id_pilot, want.daytrip, want.start_month, want.start_day
from have
inner join want
on have.daytrip = want.daytrip
and have.start_month = want.start_month
and have.start_day = want.start_day
The 'on' clause is executed on each row comparison between the two tables and the only absolute is that it must return true or false. So any column from either table can be used in the evaluation in any combination.
'on'子句在两个表之间的每一行比较上执行,唯一的绝对是它必须返回true或false。因此,任何一个表中的任何列都可以在任何组合中用于评估。
#1
2
To give an example of what Marc B is saying, the join clause can compare the daytrip, start_month and start_date columns between the have and want tables, e.g.
举一个Marc B所说的例子,join子句可以比较have和want表之间的daytrip,start_month和start_date列,例如:
select have.id_pilot, want.id_pilot, want.daytrip, want.start_month, want.start_day
from have
inner join want
on have.daytrip = want.daytrip
and have.start_month = want.start_month
and have.start_day = want.start_day
The 'on' clause is executed on each row comparison between the two tables and the only absolute is that it must return true or false. So any column from either table can be used in the evaluation in any combination.
'on'子句在两个表之间的每一行比较上执行,唯一的绝对是它必须返回true或false。因此,任何一个表中的任何列都可以在任何组合中用于评估。