Frequently, at work, I will be in a situation where I import a table of data concerning a small number of patients (typically <1000 records), and where the only field in this table that refers to the patient is his/her NHS number.
The next stage of the job is then to use this NHS number to query the main database (est. 3 million+ records) to obtain their names, dates of birth etc.
One thing I have noticed is that, after building my query in design view, if I peek at the SQL then it differs according to how I ‘drew’ the relationships.
For instance if I drag the NHS number from the main_database table onto the patient_data table I get the following:
SELECT patient_data.[nhs number], main_database.name, main_database.address, main_database.dob
FROM main_database INNER JOIN patient_data ON main_database.[nhs number] = patient_data.[nhs number];
Whereas if I make the relationship in the reverse direction I get this:
SELECT patient_data.[nhs number], main_database.name, main_database.address, main_database.dob
FROM patient_data INNER JOIN main_database ON patient_data.[nhs number] = main_database.[nhs number];
Now I know nothing about SQL, but I can see they are different. Does it make any difference to the way the computer carries the process out?
You see, if I was doing it physically myself (God forbid), it would seem the best way would be to take a record from the patient_data table and then look for the matching one in the main_database table, rather than the other way. Ok I’ll shut up now, over to you…
The next stage of the job is then to use this NHS number to query the main database (est. 3 million+ records) to obtain their names, dates of birth etc.
One thing I have noticed is that, after building my query in design view, if I peek at the SQL then it differs according to how I ‘drew’ the relationships.
For instance if I drag the NHS number from the main_database table onto the patient_data table I get the following:
SELECT patient_data.[nhs number], main_database.name, main_database.address, main_database.dob
FROM main_database INNER JOIN patient_data ON main_database.[nhs number] = patient_data.[nhs number];
Whereas if I make the relationship in the reverse direction I get this:
SELECT patient_data.[nhs number], main_database.name, main_database.address, main_database.dob
FROM patient_data INNER JOIN main_database ON patient_data.[nhs number] = main_database.[nhs number];
Now I know nothing about SQL, but I can see they are different. Does it make any difference to the way the computer carries the process out?
You see, if I was doing it physically myself (God forbid), it would seem the best way would be to take a record from the patient_data table and then look for the matching one in the main_database table, rather than the other way. Ok I’ll shut up now, over to you…