How can i insert multiple values into one row?

My query

insert into table_RekamMedis values ('RM001', '1999-05-01', 'D01', 'Dr Zurmaini', 'S11', 'Tropicana', 'B01', 'Sulfa', '3dd1');

i cant insert two values into one row. is there another way to do it?

CodePudding user response：

I'm ignorant of the human language you use, so this is a guess.

You have two entities in your system. One is dokter, the other is script (prescription). Your requirement is to store zero or more scripts for each dokter. That is, the relationship between your entities is one-to-many.

In a relational database management system (SQL system) you do that with two tables, one per entity. Your dokter table will contain a unique identifier for each doctor, and the doctor's descriptive attributes.

CREATE TABLE dokter(
  dokter_id  BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL,
  nama       VARCHAR (100),
  kode       VARCHAR(10),
  /* others ... */
);

And you'll have a second table for script

CREATE TABLE script (
  script_id  BIGINT  AUTO_INCREMENT PRIMARY KEY NOT NULL,
  dokter_id  BIGINT  NOT NULL,
  kode       VARCHAR(10),
  nama       VARCHAR(100),
  dosis      VARCHAR(100),
  /* others ... */
);

Then, when a doctor writes two prescriptions, you insert one row in dokter and two rows in script. You make the relationship between script and dokter by putting the correct dokter_id into each script row.

Then you can retrieve this information with a query like this:

SELECT dokter.dokter_id, dokter.nama, dokter.kode,
       script.script_id, script.kode, script.nama, script.dosis
  FROM dokter
  LEFT JOIN script ON dokter.dokter_id = script.dokter_id

Study up on entity-relationship data design. It's worth your time to learn and will enhance your career immeasurably.

CodePudding user response：

You can't store multiple values in a single field but there are various options to achieve what you're looking for.

If you know that a given field can only have a set number of values then it might make sense to simply create multiple columns to hold these values. In your case, perhaps Nama obat only ever has 2 different values so you could break out that column into two columns: Nama obat primary and Nama obat secondary.

But if a given field could have any amount of values, then it would likely make sense to create a table to hold those values so that it looks something like:

And then you can combine that with your original table with a simple join:

SELECT * FROM table_RekamMedis JOIN table_NamaObat ON table_RekamMedis.NoRM = table_NamaObat.NoRM

The above takes care of storing the data. If you then want to query the data such that the results are presented in the way you laid out in your question, you could combine the multiple NamaObat fields into a single field using GROUP_CONCAT which could look something like:

SELECT GROUP_CONCAT(NamaObat SEPARATOR '\n')
...
GROUP BY NoRM