Question: **I have this two table.**

```
 CREATE TABLE doctor(
    code_doctor char(5) primary key not null,
    name varchar(30) not null,
    gender char(1) check(gender='L' or gender='P'),
    address varchar(30),
    salary numeric
    )

CREATE TABLE schedule_doctor(
code_schedule char(5) primary key not null,
day varchar(10) CHECK (day IN ('monday', 'tuesday', 'wednesday', 'thursday', 'friday','saturday')),
shift varchar(10) CHECK (shift='morning' or shift='evening'),
code_doctor char(5) foreign key references doctor(code_doctor) on update cascade on delete
cascade
)

```

**How to show doctor name, day, shift in one table?**

Answer: 
you can using `join`, A `JOIN` clause is used to combine rows from two or more tables

```
SELECT d.NAME AS DoctorName,
   sd.DAY,
   sd.shift
FROM   doctor d
   INNER JOIN schedule_doctor sd
        ON  d.code_doctor = sd.code_doctor 

```

Or The **LEFT JOIN** keyword returns all records from the left table `doctor`, and the matched records from the right table `schedule_doctor`. The result is `NULL` from the right side, if there is no match.

```
SELECT d.NAME AS DoctorName,
   sd.DAY,
   sd.shift
FROM   doctor d
   LEFT JOIN schedule_doctor sd
        ON  d.code_doctor = sd.code_doctor 

```
[SQL Fiddle](http://sqlfiddle.com/#!18/d0427/1)

**MS SQL Server 2017 Schema Setup**:

```
 CREATE TABLE doctor(
    code_doctor char(5) primary key not null,
    name varchar(30) not null,
    gender char(1) check(gender='L' or gender='P'),
    address varchar(30),
    salary numeric
    )

CREATE TABLE schedule_doctor(
code_schedule char(5) primary key not null,
day varchar(10) CHECK (day IN ('monday', 'tuesday', 'wednesday', 'thursday', 'friday','saturday')),
shift varchar(10) CHECK (shift='morning' or shift='evening'),
code_doctor char(5) foreign key references doctor(code_doctor) on update cascade on delete
cascade
)

INSERT INTO doctor (code_doctor,name,gender,address,salary)VALUES('SL','Sam LeBalnc','L','USA',25000),
                                                                 ('MG','Maria Gilles','P','Spain',35000)
INSERT INTO schedule_doctor (code_schedule,day,shift,code_doctor) VALUES ('SL1','monday','morning','SL'),
                                                 ('SL2','tuesday','evening','SL'),
                                                 ('MG1','tuesday','evening','MG'),
                                                 ('MG2','thursday','evening','MG')

```

**Query 1**:

```
SELECT name, day, shift  FROM
doctor doc 
 LEFT JOIN schedule_doctor sd on doc.code_doctor  = sd.code_doctor 

```

**[Results](http://sqlfiddle.com/#!18/d0427/1/0)**:

```
|         name |      day |   shift |
|--------------|----------|---------|
| Maria Gilles |  tuesday | evening |
| Maria Gilles | thursday | evening |
|  Sam LeBalnc |   monday | morning |
|  Sam LeBalnc |  tuesday | evening |

```

You can know more about [SQL JOINS](https://www.w3schools.com/sql/sql_join.asp).