Hello,
I have a database that looks like this:
Patiens:
PatientID
Name
Surname
…
Exams:
ExamID
PatientID
Type
…
That is: many exams for one patient. The “Type” column has things like “Rx”, “TC”, …
How would a write a query that returns: “Name”, “Surname” from Patients IF a patient has done both “Rx” AND “TC” exams?
When you create the relationship between the tables Patients and Exams, and the respective PatientID fields, you will right mouse click on the relationship line in the GUI and Edit that relationship. In the following dialog box, you will select either RIGHT or LEFT join, depending on which table will be the master table and which will be the child. I cannot say for sure which it will be (LEFT or RIGHT) without seeing the precise setup, but the dialog box will describe the attributes of the join and you will want the Patients table to display all records and the other to display only the records that match values in the first table. If done this way, add a constraint NULL (which will be translated to IS EMPTY in HSQLDB) to the PatientID field from the Exams table and the query will only display records that have a matching entry for Exam.PatientID. To remove duplicate PatientID entries, use the function GROUP on the Patients.PatientID field, and optionally, COUNT the number of instances of a non-null field in a second column.
Here is a link to the part of the Base Handbook describing this functionality.
(if this answered your question, please accept the answer by clicking the check mark (
) to the left)
May be so:
SELECT "Patiens"."Name", "Patiens"."Surname"
FROM "Patiens",
( SELECT DISTINCT "PatientID" FROM "Exams" WHERE "Type" = 'Rx' ) "E1",
( SELECT DISTINCT "PatientID" FROM "Exams" WHERE "Type" = 'TC' ) "E2"
WHERE "Patiens"."ID" = "E1"."PatientID"
AND "Patiens"."ID" = "E2"."PatientID"