There are tables:

  1. Tickets (designed to issue a voucher to the doctor at the hospital) it consists of the following fields:

    1. IdTicket
    2. DoctorKod (doctor code from the Doctors table)
    3. RegistrationKod (registration code from the Registration table)
    4. DateReception (reception date)

  2. Registration (intended for registration of the patient in the hospital), it consists of the following fields:

    1. IdRegistration
    2. DateRegistration
    3. ParticientKod (patient code from the Patients table)

  3. Patients:

    1. IdParticient
    2. Fio
    3. Address
    4. CityKod

How can I get data from the Tickets table, but so that the patient's FIO is displayed from the Patients table, i.e. You need to display in the ticket the name of the patient who made an appointment with the doctor.

I tried to do through the inner join , but not very. In general, there is an idea to simply add a patient code to the Registration table, but if there is an opportunity to get around this, it would be cool.

Below I have attached the sql in which I do all this, but instead of the full name of the patient, I’ll enter the date of his registration (from the Registration table).

 SELECT Doctors.IdDoctor, Doctors.FIO, Registrations.IdRegistration, Registrations.DateRegistration, Tickets.DateReception FROM ( ( Tickets INNER JOIN Doctors ON Tickets.DoctorKod = Doctors.IdDoctor ) inner join Registrations on Tickets.RegistrationKod = Registrations.IdRegistration )

    2 answers 2

    you just need to link all the necessary tables by key columns.

    like that:

    SQL feeddle

    MySQL 5.6 Schema Setup :

     create table bilety (id int, doktor int, registracija int); create table registracii (id int, pacient int); create table pacienty (id int, fio text); create table doktora (id int, fio text); insert into bilety values (1, 1, 1); insert into registracii values (1, 1); insert into pacienty values (1, 'пациентов п.п.'); insert into doktora values (1, 'докторов д.д.');

    Query 1 :

     select doktora.id as doktor, doktora.fio as doktor_fio, registracii.id as registracija, pacienty.fio as pacient_fio from bilety, registracii, pacienty, doktora where bilety.doktor = doktora.id and bilety.registracija = registracii.id and registracii.pacient = pacienty.id

    Results :

     | id | fio | id | fio | |----|---------------|----|----------------| | 1 | докторов д.д. | 1 | пациентов п.п. |
    • Cyrillic: sqlfiddle.com/#!6/c3888/1 - msi 8:52 pm
    • @msi, thanks, I will know, but I was wrong - the question was about mysql, not ms / sql. I have already made adjustments in response. - aleksandr barakin

    The question is removed, I just corrected

     SELECT Doctors.IdDoctor, Doctors.FIO, Registrations.IdRegistration, Registrations.DateRegistration, Particients.FIO, Tickets.DateReception FROM ( ( Tickets INNER JOIN Doctors ON Tickets.DoctorKod = Doctors.IdDoctor ) inner join Registrations on Tickets.RegistrationKod = Registrations.IdRegistration ) inner join Particients on Registrations.ParticientKod = Particients.IdParticient