0

I have two tables, one with a list of projects and who is assigned to each project, and one with names and information about contractors.

"Projects_table"
Project_ID    Contractor_assigned    Job_Complete
1             Jim Smith              1
2             John Smith             0
3             Edward Smith           0
4             Smith Smith            1
5             Candle Stick           1

and

"Contractors_Table"
Contractor_ID    Name              Drywall     Insulation
1                Jim Smith         1           0
2                John Smith        0           1
3                Edward Smith      0           1
4                Smith Smith       0           1
5                Jack BeNimble     0           1
6                Jack BeQuick      1           0
7                Candle Stick      0           1

What I need, is to pull an SQL query that gives me a list of the Contractors of a certain type (Drywall or Insulator) who are currently not assigned to any job, so long as that job is also not marked "Complete."

I've tried a few versions of this, but with no success:

SELECT
Name FROM Contractors_Table
WHERE Contractors_Table.Insulation=1
AND Name NOT IN
(SELECT Contractor_assigned FROM Projects_table WHERE `Job_Complete` = 0)

What I'm hoping for output from my above example is to get back:

Jack BeNimble
Candle Stick

Those two Insulators are not currently assigned a job, excluding the "complete" job.

Graeme
  • 41
  • 3
  • Ummm. I don't understand this: "not assigned to any job, so long as that job is also not marked "Complete." If they are not assigned to any job, how can that (non-existent) job be marked as anything? – Gordon Linoff Jan 30 '17 at 15:55
  • outer join will do it – Dave Jan 30 '17 at 15:56
  • @GordonLinoff I guess what I meant is that the "Projects_Table" is quite large, and so Jim Smith for example could have been assigned to 50 projects in the past, but they are all now "Complete." I need a list of Contractors who are available for work now, which means I need to know if he's not currently assigned to any other Active job. I know, clear as mud... – Graeme Jan 30 '17 at 16:01
  • What about JOINS http://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join#38578 ? So use `Full outer join` with additional check if `projects_table.field` is `NULL`. – JustOnUnderMillions Jan 30 '17 at 16:07

3 Answers3

1

You should use NOT EXISTS instead:

SELECT Name
  FROM Contractors_Table c
 WHERE c.Insulation = 1
   AND NOT EXISTS
       (SELECT 1 
          FROM Projects_table p
         WHERE p.Job_Complete = 0
           AND p.Contractor_assigned = c.Name)

You could refactor the query you are trying to do, but will take much more effort to use NOT IN.

You should also use foreign keys instead of names to compare data. You are duplicating information in relational database, which doesn't follow Normalization rules

Felippe Duarte
  • 14,901
  • 2
  • 25
  • 29
1

Firstly your table design is wrong: you should store the Contractor_ID in the project table, and not their names (what happens with homonyms???)

Secondly I don't understand why your query doesnt return the expected result because it looks correct. Maybe because you have a column named Name, and this is a reserved keyword, and you should thus surround it with `

Anyway it's never efficient to do a NOT IN

You can rewrite that with an OUTER JOIN :

SELECT C.`Name` 
FROM Contractors_Table C
LEFT JOIN Projects_table P ON C.`Name` = P.`Contractor_assigned` AND P.`Job_Complete` = 0
WHERE C.Insulation=1
  AND P.ID IS NULL --> This will ensure that you only get those who are not in the Project table
Thomas G
  • 9,886
  • 7
  • 28
  • 41
0

Obviously a couple of queries (one for each type). If you use unique ID's in both tables for each of the contractors you can join the tables and select where Contractors_Table.drywall = 0 and Project_Table = 0 (Group by Contractor ID).

I think its just about getting your data sorted in your tables then writing the query.

HTH :-)

Bowcaps
  • 127
  • 2
  • 11