I have 4 tables which need to be joined. They are:
I also need to be able to count the number of contractors in each crew.
I have created some SQL (MySql) which does the job nicely:
Select contractors.crew_id as contractors_crew_id, auburntree.crews.crew_name, count(*) as members, skill_type.skill, location.location_name
JOIN auburntree.crews on contractors.crew_id=crews.id
JOIN skill_type on skill_type.skill_id = crews.skill_id
JOIN location on location.id = crews.location_id
GROUP BY contractors.crew_id ;
The contractors table contains a foreign key reference to which crew they are assigned to. Hence the column "contractors_crew_id"
I get a nice result, as you can see in this screen capture image:
Problem Defined: I need to define an empty crew first before I add contractors/members. I will give it a name, a skill and a location. I might define an empty crew several days in advance. Currently an empty crew does not show up in my results.
I want to see:
contractors_crew_id = Null, crew_name, skill_type, Location, members 0
I have tried using RIGHT OUTER JOIN on my tables and I still do not see empty crew. LEFT OUTER JOIN does not work either.
contractors_crew_id will be Null at that point, as no contractors have been assigned yet.
I Hope this makes, sense - sorry it is a bit long and complicated. I have tried really hard to explain in.
Many Thanks !!