nick nick - 6 months ago 14
MySQL Question

How can I use a foreign key and joins to get the info I need from mysql database

I am currently achieving this with several steps, but it would be better if I could find what I need with one query. I think it's possible with joins and a foreign key, but I can't really figure out how it all works together.

Anyway, I have two tables

doctors
and
appointments
, one column linked with a foreign key (fk is
doctor_id
in appointments referencing
id
in
doctors
) and I need to get an appointment date, within a certain range of time and link it by the
doctor_id
to the matching id in
doctors
table and get the name and phone number from that table as well.

Here is the join statement I came up with:

SELECT a.doctor_id, a.appointment_date, b.last_name, b.preferred_phone
FROM docflight_appointments a, docflight_doctors b
WHERE a.appointment_date BETWEEN NOW() AND NOW() + INTERVAL 3 DAY


enter image description here

Which gives me all the doctors and says their ids are all 3, which is not the case (it's an auto incrementing column), but it is the doctor
id
linked to the appointment that I need to match with. So how can I achieve that with a foreign key?

result

Answer

You can add the condition for the join : a.doctor_id = b.id

SELECT a.doctor_id, a.appointment_date, b.last_name, b.preferred_phone 
FROM docflight_appointments a, docflight_doctors b 
WHERE a.doctor_id = b.id
  AND a.appointment_date BETWEEN NOW() AND NOW() + INTERVAL 3 DAY