Select Query "Where" Problem

[Pringle9984]

I'm using the following query

SELECT job.job_skyjob, sky_promocodes.promo_id
digibox.*, customer.*, engineer.engineer_code, job.
FROM ((sky_promocodes INNER JOIN (job INNER JOIN custome
ON job.customer_id = customer.customer_id) O
sky_promocodes.promo_id = job.job_promocode) LEFT JOIN digibox O
job.job_number = digibox.job_id) INNER JOIN engineer O
job.engineer_id = engineer.engineer_i
WHERE (((job.job_skyjob)=Yes)
ORDER BY job.job_number

It *should* find the details tied to every job where job.job_skyjob
Yes; and it almost works; however it will only show the jobs wher
job.job_skyjob = Yes and where the promocode is not null

Any suggestions on how to fix this problem

 

[[MVP] S.Clark]

When you add the WHERE to the outer join, it treats it like an INNER join.

You can break it out to two queries. One to do the WHERE the other to do
the LEFT.

 

[John Spencer]

You will probably have to change the join involved to a Left Join (or
possibly a right join) since you are joining on the promoCode. This may
give you an ambiguous join error if you try to use the right join.

I always have a problem envisioning these multi-table joins which is the
reason I like using the query grid when possible to build the query. Here
is a stab at rewriting the FROM clause. No guarantees.

SELECT sky_promocodes.promo_id,
digibox.*, customer.*, engineer.engineer_code, job.*
FROM ((((job LEFT JOIN Customer ON job.customer_id = customer.customer_id )
LEFT JOIN sky_promocodes job.job_promocode = sky_promocodes.promo_id )
LEFT JOIN digibox ON job.job_number = digibox.job_id)
LEFT JOIN engineer ON job.engineer_id = engineer.engineer_id)
WHERE job.job_skyjob=Yes