We help IT Professionals succeed at work.

Problem with query

177 Views
Last Modified: 2010-03-19
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'
)
Comment
Watch Question

Commented:
Try using LEFT JOIN instead of JOIN
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks guys, neither worked.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
for good practice you should also specify all of the table aliases when you reference columns to avoid ambiguity in the query.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.