Steve Whitlow Steve Whitlow - 6 months ago 21
MySQL Question

Can 4 fields be put together to use as a key to join two tables?

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 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?


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.