POSTGRES - Combine same columns with different text in one row

Question

I have three tables that i want to combine. I have a table doctor, patient and operation. Doctor represents all cind of doctors. Patient represents a patient and operation holds primaryKeys from patient and doctor. How do i write a query which will show me chiefDoctor and assistantDocotor and Patient? What did i reach so far?

Show firstname and lastname from one either Chiefdoctor or Assistantdoctor. How do i write to show a table with both Chief and Assistant in it?

select a.vorname|| ' ' || a.nachname AS leitenderArzt, p.firstname || ' ' || p.lastname AS patient
from angestellter a inner join operation o on a.id = o.leitenderarzt
inner join patient p on o.patientident=p.ident

| id | firstname       | lastname      | patient      |
| 1  | ImA             | ChiefDoctor1  | p.firstname  |
| 3  | ImA             | ChiefDoctor3  | p.firstname  |

The underlying structure of my database with a representation.

CREATE TABLE doctor
(
  id serial NOT NULL,  
  firstname character varying(255) NOT NULL,
  lastname character varying(255) NOT NULL,  
  CONSTRAINT angestellter_pkey PRIMARY KEY (id),
}

 Table doctor
 |id | firstname | lastname        |
 | 1 | ImA       | ChiefDoctor1    |
 | 2 | ImA       | AssistantDoctor |
 | 3 | ImA       | ChiefDoctor2    |

CREATE TABLE patient
(
  ident serial NOT NULL,  
  firstname character varying(255) NOT NULL,
  lastname character varying(255) NOT NULL,
  CONSTRAINT patient_pkey PRIMARY KEY (ident),
}

Table patient
| ident | firstname | lastname |
| 1     | Operated  | ME       |

CREATE TABLE operation
(
  id serial NOT NULL,
  chiefDoctor integer NOT NULL,
  AssistantDoctor integer NOT NULL,
  patientident integer NOT NULL,
  CONSTRAINT operation_pkey PRIMARY KEY (id),
  CONSTRAINT fkoperation539608 FOREIGN KEY (patientident)
      REFERENCES patient (ident) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkoperation745809 FOREIGN KEY (assistantDoctor)
      REFERENCES angestellter (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkoperation949671 FOREIGN KEY (chiefDoctor)
      REFERENCES angestellter (id) MATCH SIMPLE
}

Table operation
| id | doctorID | doctorID | patientID |
| 1  |    1     |    2     |     1     |

How do i write a query which will show show who operated a patient with full name? It should look like this.

| id | chiefdoctor       | assistantdoctor      | patient      |
|----| ImA + ChiefDoctor |ImA + AssistantDoctor | Operated + ME|

You consequently used a } to terminate the table definitions. And omitted the ; — wildplasser
– wildplasser, Commented Jun 28, 2015 at 20:01
Operation table ist krank.It Is a table many to many but is not really many to many. If in the staff comes another doctor what you are going to do? I would improve the architecture with a table operation and another table many to many. Than you will join whatever you want — jurhas
– jurhas, Commented Jun 28, 2015 at 20:17
If in the staff comes another doctor what you are going to do? I will INSERT INTO doctor VALUES (4, ImA, ChiefDoctor4); The query will show me that doctor as well or am i missing something? I add a new doctor and if he does an operation i will add his id in operation "4", "someAssistant", "some patient". — Matthis Kohli
– Matthis Kohli, Commented Jun 28, 2015 at 20:51

Lennart - Slava Ukraini · Accepted Answer · 2015-06-28 19:33:18Z

3

The first and perhaps most intuitive way of doing this is to join doctor twice:

select o.id
     , d1.firstname || ' + ' || d1.lastname as chiefdoctor
     , d2.firstname || ' + ' || d2.lastname as assistantdoctor
     , p.firstname || ' + ' || p.lastname as patient
from operation o
join doctor d1
    on o.chiefDoctor = d1.id
join doctor d2
    on o.AssistantDoctor = d2.id
join patient p
    on o.patientident = p.ident

You probably would like to trim names as in:

trim(both from d1.firstname) || ' + ' || trim(both from d1.lastname)

but I got the feeling that this was not the major concern you had so inorder to keep the solution shorter, I left that out

answered Jun 28, 2015 at 19:33

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthis Kohli Over a year ago

perfect. I will try the trim later because im sitting all day on this. Thank you very much.

Collectives™ on Stack Overflow

POSTGRES - Combine same columns with different text in one row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related