Problem with query

Hi,  I have a list of records in a main table that has two other tables associated with it so that when a record is updated the update is inputed into the details and analysis table.  So I have written a query that creates a join showing all records that have been updated, but I would like to find out which records haven't been updated, so I have added an outer query selecting everything in the main table that doesn't appear in the join showing updated records.  The problem is, the query ends up showing all records updated or not, here is the query can someone see what I'm doing wrong or suggest a different way to write a query showing all records in the first table that doesn't have updated id's in the second & third tables?  Thank you in advance.

select *
from MCommPLOrders as a
where convert(varchar, a.timeofcode, 112) =
convert(varchar, getdate() -2, 112)
and
(jobtypeid not like '%code%' and jobtypeid not like '%COIN%' and jobtypeid not like '%FRAME%')
and workgrpid not like '%COIN%' and
reasoncode IN ('pl', 'pl2', 'r2', 't2', 'r16', 't16') and
(troublecategory <> '5' or troublecategory is null) and
LEFT(custphoneno, 3) <> '807' and
npa <> '705 North' or
LEFT(custphoneno, 4) = ':269H'
and id not in
(
select id
from MCommPLOrders as a
join MCommPLOrderDetails as b
on a.id = b.jobid COLLATE Latin1_General_CI_AS
join MCommPLOrderAnalysis as c
on a.id = c.jobid COLLATE Latin1_General_CI_AS
where convert(varchar, a.timeofcode, 112) =
convert(varchar, getdate() -2, 112)
and
(jobtypeid not like '%code%' and jobtypeid not like '%COIN%' and jobtypeid not like '%FRAME%')
and workgrpid not like '%COIN%' and
reasoncode IN ('pl', 'pl2', 'r2', 't2', 'r16', 't16') and
(troublecategory <> '5' or troublecategory is null) and
LEFT(custphoneno, 3) <> '807' and
npa <> '705 North' or
LEFT(custphoneno, 4) = ':269H'
)
marcus72Asked:
Who is Participating?
 
LimbeckCommented:
hi,maybe this:

LEFT(custphoneno, 3) <> '807' and
npa <> '705 North' or                              // <--
LEFT(custphoneno, 4) = ':269H'

(LEFT(custphoneno, 3) <> '807' and
npa <> '705 North' or
LEFT(custphoneno, 4) = ':269H')
0
 
OtanaCommented:
Try using LEFT JOIN instead of JOIN
0
 
marcus72Author Commented:
thanks guys, neither worked.
0
 
CragCommented:
It might be a red-herring but in the sub-query you have:
select id
from MCommPLOrders as a
join MCommPLOrderDetails as b

and the a alias is used again for the same table as in the main part of the query.
Have you tried changing this to a different alias for the sub-query to avoid confusion?
0
 
CragCommented:
for good practice you should also specify all of the table aliases when you reference columns to avoid ambiguity in the query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.