Linking tables when grouping

Question

Hi can anyone help me with this

I have this query I am developing

select X.col1, X.col2, X.col3, X.col4
from table X
join (select col3, min(col4) as mcol4 from table group by col3) as Y
    on X.col3=Y.col3 and X.col4=Y.mcol4
where X.col2='xxx';

Thanks to Draemon and this works great
What I'd like to do if possible is to link X.col1 to reference another table
i.e X.Col1 say equals 123
Now in another table we have:

Patient No, Patient
123       , Ben
567       , Peter

What I'd like to do is make X.Col1 = Ben etc
Thanks once again guys.

Answer 1

You can just join it on to the patient names table lie this:

select X.col1, X.col2, X.col3, X.col4, patient_names.patient
from table X
join (select col3, min(col4) as mcol4 from table group by col3) as Y
    on X.col3=Y.col3 and X.col4=Y.mcol4
INNER JOIN patient_names ON X.col1 = patient_names.patient_no
where X.col2='xxx';