CODEWITHSUNDEEP
phpmysqlsql
Graeme
Graeme

Reputation: 41

PHP/SQL: How to SELECT values from one table that do not exist in a completely different table

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.

Upvotes: 0

Views: 58

Answers (3)

Felippe Duarte
Felippe Duarte

Reputation: 15131

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

Upvotes: 1

Thomas G
Thomas G

Reputation: 10226

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

Upvotes: 1

Bowcaps
Bowcaps

Reputation: 127

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 :-)

Upvotes: 0

Related Questions