-- Create table pacientes
CREATE TABLE IF NOT EXISTS pacientes (
id INTEGER PRIMARY KEY,
nombre TEXT,
edad INTEGER
);
-- Create table vademecum
CREATE TABLE IF NOT EXISTS vademecum (
id INTEGER PRIMARY KEY,
medicina TEXT,
precio INTEGER
);
-- Create table intermediate pacientes_vademecum for relation many to many
CREATE TABLE IF NOT EXISTS pacientes_vademecum (
pacientes_id INTEGER,
vademecum_id INTEGER,
FOREIGN KEY (pacientes_id) REFERENCES pacientes(id),
FOREIGN KEY (vademecum_id) REFERENCES vademecum(id),
PRIMARY KEY (pacientes_id, vademecum_id)
);
-- Insert data into the pacientes table only if it does not already exist
INSERT INTO pacientes (nombre, edad)
SELECT 'Juan', 24
WHERE NOT EXISTS (
SELECT 1 FROM pacientes WHERE nombre = 'Juan'
);
-- Insert data into the vademecum table only if it does not already exist
INSERT INTO vademecum (medicina, precio)
SELECT 'Aspirina', 251
WHERE NOT EXISTS (
SELECT 1 FROM vademecum WHERE medicina = 'Aspirina'
);
-- Insert relation pacientes - vademecum
INSERT INTO pacientes_vademecum (pacientes_id, vademecum_id)
VALUES (
(SELECT id FROM pacientes WHERE nombre = 'Juan'),
(SELECT id FROM vademecum WHERE medicina = 'Aspirina')
);
-- Select medicina for Juan
SELECT v.medicina
FROM pacientes p
JOIN pacientes_vademecum pv ON p.id = pv.pacientes_id
JOIN vademecum v ON pv.vademecum_id = v.id
WHERE p.nombre = 'Juan';
-- Select nombre for Aspirina
SELECT p.nombre
FROM pacientes p
JOIN pacientes_vademecum pv ON p.id = pv.pacientes_id
JOIN vademecum v ON pv.vademecum_id = v.id
WHERE v.medicina = 'Aspirina';
In this version we can add the number of medicine boxes assigned to each patient, for example to Miguel: 2 boxes of Aspirina, 3 boxes of Ansiolitic and 1 box of Dolorin.
-- Create table intermediate pacientes_vademecum for relation many to many
CREATE TABLE IF NOT EXISTS pacientes_vademecum (
pacientes_id INTEGER,
vademecum_id INTEGER,
cantidad INTEGER,
FOREIGN KEY (pacientes_id) REFERENCES pacientes(id),
FOREIGN KEY (vademecum_id) REFERENCES vademecum(id),
UNIQUE (pacientes_id, vademecum_id)
);
-- Insert relation pacientes - vademecum
INSERT INTO pacientes_vademecum (pacientes_id, vademecum_id, cantidad)
VALUES (
(SELECT id FROM pacientes WHERE nombre = 'Juan'),
(SELECT id FROM vademecum WHERE medicina = 'Aspirina'),
3
);
-- Select medicina for Juan
SELECT v.medicina, pv.cantidad
FROM pacientes p
JOIN pacientes_vademecum pv ON p.id = pv.pacientes_id
JOIN vademecum v ON pv.vademecum_id = v.id
WHERE p.nombre = 'Juan';