mysql多表查询及多表同字段名拼接

我这里3表连接其中t2和t3的name和code需要拼接，通过t1.purpose分辨何时获得哪个name，code。ifnull判定不为空的值设为sitename或sitecode。

select
        t1.*,
        ifnull(t2.name,t3.name) as siteName,
        ifnull(t2.code,t3.code) as siteCode
        from face_equipment t1
        left join face_train_site t2 on t1.site_id = t2.id and t1.purpose != 3
        left join face_classroom t3 on t1.site_id = t3.id and t1.purpose = 3
        where t1.del_flag = 0