• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

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'
)
0
marcus72
Asked:
marcus72
2 Solutions
 
OtanaCommented:
Try using LEFT JOIN instead of JOIN
0
 
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
 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now