'Show different values from the same joined table on one row

There are two doctors (DOC_A and DOC_B) assigned to every patient. There are 3 tables. The PATIENT table has the Patient_ID and DOC_A's DOC_ID. The CONSULT table has Patient_ID and DOC_B's DOC_ID. Finally, the DOCTOR table has both DOC_A and DOC_B's DOC_ID with their FirstName and LastName.

I'd like to show a distinct Patient_ID on each row with both DOC_A and DOC_B with FirstName and LastName (concatenated) instead of DOC_ID's.

Example Output (manually created):

Patient_ID DOC_A FirstLast DOC_B FirstLast
123 John Smith Jane Smith
456 Nathaniel Hawkeye Cora Munroe


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source