What is the best JOIN for this? (Many to Many) MYSQL

For these tables here (circled)

http://imgur.com/Q1HlJ

What would the best join be to use for them , I tried using OUTER join but it would not return any rows even if there was match开发者_如何学JAVAing data.

Thanks

QUERY

select *

from 

hr

OUTER JOIN

hr

ON

hr.procedure_id = procedure.procedure_id

OUTER JOIN

staff

ON

staff.staff_id = hr.staff_id

Where hr.procedure_id = procedure.procedure_id

I would suggest reading up on JOINs. It is hard to know what you are looking for. We won't make that decision for you.

What you probably want is this:

select p.*, hr.*, s.*
from procedure p
left outer join hr on p.procedure_id = hr.procedure_id
left outer join staff s on hr.staff_id = s.staff_id

That will give you results unless there are no rows in procedure.

There are different ways that you could put these together, though, depending on what exactly you want. I don't know what "hr" stands for here, so I can't use common sense to figure that out.

Don't bother with JOIN at all, and let the query optimizer handle it for you.

select * from hr, procedure, staff
where hr.procedure_id = procedure.procedure_id and staff.staff_id = hr.staff_id