Ask Your Question

Query in one-to-many relationship

asked 2015-04-15 14:30:19 +0200

otonvm gravatar image


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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2015-04-15 15:19:15 +0200

JohnSUN gravatar image

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"
edit flag offensive delete link more

answered 2015-04-15 15:08:52 +0200

doug gravatar image

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 (image description) to the left)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-04-15 14:30:19 +0200

Seen: 176 times

Last updated: Apr 15 '15