- New version, add quantity of boxes.
- 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.
p55H_Sqlite_Farmacia_v2.aia (35.3 KB)
-- 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';