MySQL join problem

Question

Table1 has u_name, Table2 has u_name, u_type and u_admin

1. Table1.u_name is unique. But neither of the 3 fields in Table2 is unique.

2. For any value of Table1.u_name, there are 0 to many entries in Table2 that Table2.u_name equals to that value.

3. For any value of Table1.u_name, there are 0 to 1 entries in Table2 that Table2.u_name equals to that value AND Table2.u_type='S'

What I want: Use Table1.u_name to get Table1.*, Table2.u_admin where Table1.u_name=Tabl2.u_name and Table2.u_type='S'. If there is no such entry in Table2 we still need to get Table1.*

Please help give me some hints. Thank you so much!

Answer 1

I suggest you read about "outer join", specifically "left outer join", which is what you want. An outer join returns all rows from one table, returning nulls for values from the joined table where there is no row matching the key.

Either of these should work

select table1.*, table2.u_admin from table1 left join table2
on table1.u_name=table2.u_name and table2.type='S'

Or

with a as (select u_name,u_admin from table2 where type='S')
select table1.*,a.u_admin from table1 left join a on table1.u_name=a.u_name