0

I have 4 tables which need to be joined. They are:

  1. Contractors
  2. Crews
  3. Skill_type
  4. Location

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

FROM contractors

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:

https://drive.google.com/file/d/0B5elaUk7GlRoS0JWblE3ZzFXY0E/view?usp=sharing

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 !!

Vince
  • 1,405
  • 2
  • 20
  • 33

2 Answers2

2

Because you are actually focussing on information about crews, and not contractors, I would suggest to start by querying the crews table (in the FROM part of your query) and then join the other tables.

Using a LEFT JOIN on the contractors table should then give you the desired result. Since crews is now the "left" table in the query, the result will include all rows from crews, even when there is no corresponding contractor. This answer explains it very well.

This query works for me on test tables based on your examples:

SELECT 
  contractors.crew, 
  crews.crew, 
  COUNT(contractors.id) as members, 
  skills.skill, 
  locations.location 
 FROM crews
 JOIN skills ON crews.skill = skills.id
 JOIN locations ON crews.location = locations.id
 LEFT JOIN contractors ON contractors.crew = crews.id
 GROUP BY contractors.crew, crews.id;

To see for yourself, try this SQL Fiddle.

Steven Rombauts
  • 333
  • 1
  • 8
  • YOU SIR ARE A ROCK STAR !! MANY MANY THANKS ! The only thing I do not understand is that I am only getting 9 results. I have tried the query in both PHPStorm as well as Myadmin. Both give me the same - any thoughts ? Thanks Again ! – Vince May 21 '15 at 19:17
  • Heah Steven - I found an error in the query. At first I though t it was me, but in fact I have replicated it on your SQL fiddle as well. Essentially when you add more that 1 crew without members the additional crews do not show up in the result. You only get 1 crew without members. – Vince May 21 '15 at 21:19
  • @Vince That's because we group on the `contractors.crew_id` column. That will also group all the crews without any members because `contracts.crew` is null.. We need to add `crews.id` to the group by too. I've updated the answer and the fiddle. Let me know if that works for you too! – Steven Rombauts May 21 '15 at 21:42
  • You are the man !!! Many Thanks !! It's working Great ! Now All I have to do it is convert it into Laravel Query Builder ! – Vince May 21 '15 at 21:50
0

Try this, instead:

Select contractors.crew_id as contractors_crew_id, crews.crew_name, count(*) as members, skill_type.skill, location.location_name
FROM crews
LEFT OUTER JOIN contractors on contractors.crew_id=crews.id
LEFT OUTER JOIN skill_type on skill_type.skill_id = crews.skill_id
LEFT OUTER JOIN location on location.id = crews.location_id
GROUP BY contractors.crew_id ;

That should pull all crews, regardless of whether or not they have contractors assigned. The way you wrote your original query starts by looking at your contractors and then pulling in any crews they might be assigned to. Crews is clearly your focal table here, so you should be joining the rest of your tables to it, rather than to the contractors table.

Jim Stitzel
  • 121
  • 4
  • Thanks Stitzelj Sadly it still does not work. The crews without member contractors are still being ignored However, I am going to take your suggestion on board of focusing on the Crews not the contractors. – Vince May 21 '15 at 18:44
  • I just updated the query in my response above. I realized a LEFT OUTER JOIN is actually how you to join your tables because it will pull back the crews even if you haven't populated any other data for those crews in the other tables. – Jim Stitzel May 21 '15 at 18:48