I have a select statement that needs some refinement.

select PatientName, PatientID, PatientDOB from MyTable;

now this works but there is a row generated for each exam in the table.
How do I make this DISTINCT for each PatientID?

SpreadTooThin:


Are you saying that you store Patient data and Exam data in the same
table? If so, you don't have a 'distinct problem'.

Proper normalization dictates a 1:n-relationship between Patient and
Examination, so you only have one Patient tuple for each Person. Once
you have that in place, your problem is solved.

--
Erick

"If you don't know where you are going, you will probably end up
somewhere else." - Laurence J. Peter

I mean... Does the DISTINCT key word remove all duplicates in the returned select?

i.e...

SELECT DISTINCT PatientName, PatientID, PatientDOB from MyTable;

or

SELECT PatientName, DISTINCT(PatientID), PatientDOB from MyTable;

are these 'different' statements?

If there is an assumption that each patient has their own patient ID.. but may have had their names changed... then the second statement wouldn't reflect any name change where as the first might show two different patient names with the same ID and neither list would show any exact duplicates?

Indeed the table was not properly normalized.. unfortunately.. but you know what they say, "There is never time to do it right, but there is always time to do it over!".

I think my problem is more that I'm not sure how DISTINCT works.
Does it look at the entire returned record set and remove duplicate rows?

Assuming you have a table like:

X y Z
A, B, C
A, B, C
A, A, C
A, A, A
A, A, A

select DISTINCT X, Y, Z FROM table;
A, B, C
A, A, C
A, A, A

is this different than
select DISTINCT(A), B, C from table;
A, B, C

SpreadTooThin <bjobrien62 (AT) gmail (DOT) com> wrote in
news:15137999.1124.1327955311387.JavaMail.geo-discussion-forums@prhu31:

how will you ever find the time to do it over?"

No, your problem is a bad database design. Fix that, and the other problem disappears.

"SpreadTooThin" <bjobrien62 (AT) gmail (DOT) com> wrote

Can you clarify what it is that you would like to get? I wonder whether its
not "distinct" but "group by" that you might be after. In any event if you
could give an example of what you would like to get out from your query then
that would help others help you.
--
Brian Cryer
http://www.cryer.co.uk/brian

Brian Cryer wrote:
maybe not..if he has duplicate data all over the place - like a table
where instead of a link to a patent table the patient ID and name are
duplicated..

SpreadTooThin wrote:

Normalization can be done with a few database statements, see below. Most
of the thinking needs to be done *before*.

Yes.

You could begin to normalize it like this (untested):

DROP TABLE IF EXISTS `letters`;

CREATE TABLE IF NOT EXISTS `letters` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`value` VARCHAR(1) NOT NULL UNIQUE
)
SELECT DISTINCT NULL, `X` FROM `table`;

INSERT IGNORE INTO `letters`
SELECT DISTINCT NULL, `y` FROM `table`;

INSERT IGNORE INTO `letters`
SELECT DISTINCT NULL, `Z` FROM `table`;

ALTER TABLE `table`
ADD COLUMN `x_id` AFTER `X` INT(10) UNSIGNED NOT NULL,
ADD COLUMN `y_id` AFTER `y` INT(10) UNSIGNED NOT NULL,
ADD COLUMN `z_id` AFTER `Z` INT(10) UNSIGNED NOT NULL;

UPDATE `table`
LEFT JOIN `letters` ON `table`.`X` = `letters`.value
SET `table`.`x_id` = `letters`.`id`;

UPDATE `table`
LEFT JOIN `letters` ON `table`.`y` = `letters`.`value`
SET `table`.`y_id` = `letters.id;

UPDATE `table`
LEFT JOIN `letters` ON `table`.`Z` = `letters`.`value`
SET `table`.`z_id` = `letters`.`id`;

ALTER TABLE `table`
DROP COLUMN `X`,
DROP COLUMN `y`,
DROP COLUMN `Z`;

BTDT.

There are no *columns* named A, B, and C (there is *data*, e. g. field
values), so the second code will not work in the first place. If there
were, then the explanation in my other followup applied.

--
PointedEars

Please do not Cc: me. / Bitte keine Kopien per E-Mail.

Which name and which date of birth do you want for each PatientID?
It matters.

I'm assuming that this information *might* be for something like
an AIDS or sexually-transmitted-disease clinic, where patients
routinely (and perhaps deliberately) forget the name and DOB they
gave and make up a new one several times during an exam. (The
Patient ID is on a bar code on either a plastic card or on the
patient - harder to forget or fake.)

Normally, you'd want one patient table and one exam table, linked
to each other by PatientID. There may be zero (yes, ZERO. How do
you schedule an appointment for a patient if they haven't had an
exam yet? and they won't have an exam unless you can schedule one.
Oh, yes, you need an appointment table for that.) or more exams for
each patient. Patient info goes in the patient table.

Exam-specific info goes in the exam table. You may need more tables
for 1:n relationships in a given exam - for example, several X-rays
may be taken or several lab tests ordered during a single exam.
You also don't want to have a space in a table for every possible
test during an exam - I believe there are at least tens of thousands
of them, especially if this is supposed to cover exams by doctors
of all specialties, and more are being invented all the time.

It is *possible* that you want an exam-specific "patient name at
the time of the exam" field for each exam, although you'll get a
lot of howls that this is not normalized. It might be used, for
example, to double-check *PAPER* or *FILM* records against
sloppily-written patient IDs, or to keep track of the name used in
filing an insurance claim. Retroactively changing the names on
medical records is dangerous and risks errors. If you ever need
this field, it *WILL* matter which exam is involved. The patient
name you actually use should be in the patient record.

You still need a name field in the patient record (a patient with
no exams yet still needs a name), if only for rescheduling appointments.
Patients usually change their names infrequently (adoption and
marriage, among other reasons) or not at all.