PHP MySQL query WHERE equals AND row does not exist

Question

I have been looking and cant find an answer to what im trying to do. I dont know if a query can be created in the following way.

$sql_call = "SELECT table.item,table.item,table.item FROM cust 
        LEFT JOIN contact ON cust.id = contact.client_id 
        LEFT JOIN survey_audit ON cust.id = survey_audit.cust_id 
        WHERE cust.clinic='$clinic_id' AND contact.participate='1' AND survey_audit.survey_id != '$post_survey_id'";

The query above, does not do what Im trying to do, and that is:

Get data from tables WHERE cust.clinic=something AND contact.participate=something AND (this is the part im not sure about) inside Survey_audit table, there is no row with this id.

Is it possible to ask sql to find a result where something=something AND is no row in specific table?

Mike Brant · Accepted Answer · 2015-01-21 16:59:10Z

1

You are sort of on the right track. You simply need to look for cases where survey_audit.survey_id is NULL.

SELECT table.item,table.item,table.item
FROM cust 
LEFT JOIN contact
  ON cust.id = contact.client_id 
LEFT JOIN survey_audit
  ON cust.id = survey_audit.cust_id 
WHERE cust.clinic='$clinic_id'
AND contact.participate='1'
AND survey_audit.survey_id IS NULL

Here is a very useful resource for helping you determine how to form more complex join scenarios. Your case is the 4th example on this page.

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

edited Jan 21, 2015 at 16:59

answered Jan 21, 2015 at 14:47

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Over a year ago

This answer 100% works. Thank you thank you thank you!

Serpes · Accepted Answer · 2015-01-21 14:45:33Z

0

You can exclude all the elements of the table using a subquery:

$sql_call = "SELECT table.item,table.item,table.item FROM cust 
        LEFT JOIN contact ON cust.id = contact.client_id 
        LEFT JOIN survey_audit ON cust.id = survey_audit.cust_id 
        WHERE cust.clinic='$clinic_id' AND contact.participate='1' AND survey_audit.survey_id NOT IN (SELECT survey_id FROM Survey_audit);

answered Jan 21, 2015 at 14:45

Serpes

6724 silver badges14 bronze badges

2 Comments

Rimble Over a year ago

Make sure you explain what the error was. Else it's not much of an answer.

Jason Over a year ago

Wow, I never knew you could sub-query.. This is very valuable to me!

Alexandru Olaru · Accepted Answer · 2015-01-21 14:56:15Z

0

Yes it is possible, you should read more about other types of joins in mysql there are 4 types of joins

INNER JOIN (JOIN) - matching id's in both tables
LEFT JOIN - matching id in table A and can be null in Table B
RIGHT JOIN - matching id in table B and can be null Table A
OUTER JOIN - can be null in both tables

Recommend you to read the following article http://www.sitepoint.com/understanding-sql-joins-mysql-database/

So for your question I guess you should use RIGHT JOIN survey_audit instead of LEFT JOIN survey_audit

answered Jan 21, 2015 at 14:56

Alexandru Olaru

7,1426 gold badges31 silver badges55 bronze badges

Collectives™ on Stack Overflow

PHP MySQL query WHERE equals AND row does not exist

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related