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;
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 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.
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.