Re: MySQL Transact: Right Syntax

[Roland Bouman]

What version are you using? the sex column is calculated using a subquery, and those are supported in MySQL >= 4.1

However, in this particular case, you can write the query like this:

select
r.reg_man_serial_no as mrn,
r.new_ic_no as new_icno,
d.r_old_ic_no as old_icno,
d.r_name as r_name,
s.sex_code as sex
from registration_maintenance r
left join registration_maintenance_details d
on r.reg_man_serial_no=d.reg_man_serial_no
left join sex_maintenance s
on d.sex_serial_no=s.sex_serial_no
where r.reg_man_serial_no=2
and r.active_status='Y'

In fact, I would nearly always write such a query like this.