CODEWITHSUNDEEP
mysqlleft-join
owsata
owsata

Reputation: 1225

MySQL LEFT JOIN with WHERE

Ive got two tables company and process. Company has field company_id and others. Process has fields company_id and status. I want all the fields of the company table only without those in the process table that have a status 0. Ive come sofar with the following but that gives me all the values of company even with the one of process table that has status 0. 

SELECT  c.company_id
FROM    company c
LEFT JOIN 
   process p 
ON     c.company_id = p.company_id 
   AND p.status != '0'

Upvotes: 1

Views: 8700

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

SELECT c.company_id 
FROM company c 
  LEFT JOIN process p 
    ON c.company_id = p.company_id 
WHERE p.status != '0'
  OR p.status IS NULL
;

2nd solution (edited and simplified): This doesn't look like "hack", does it?

SELECT c.company_id 
FROM company c 
WHERE c.company_id NOT IN
  ( SELECT company_id
    FROM process
    WHERE status = '0'
  )
;

The problem with second solution is that if you want fields from table process to be shown, one more join will be needed (this query with process).

In the first solution, you can safely change first line to SELECT c.company_id, c.company_title, p.status or , SELECT c.*, p.*.

Upvotes: 3

AndyG
AndyG

Reputation: 41220

First, your code is performing a LEFT JOIN instead of an INNER JOIN, second you have no WHERE clause (it seems you are saying AND instead of WHERE).

Upvotes: 2

Related Questions