October 10, 2007 at 7:15 am
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'
)
October 10, 2007 at 7:59 am
Whew! That's an ugly query. Are the OR clauses supposed to apply to the whole WHERE clause or just a particular statement. Meaning is it
WHERE x = 1
and Y = 2
OR Y = 3
It makes a difference:
create table MyTable
( x int, y int, z int)
go
insert MyTable select 1, 1, 1
insert MyTable select 1, 2, 1
insert MyTable select 1, 2, 3
insert MyTable select 1, 1, 3
insert MyTable select 2, 1, 3
go
select * from mytable
where x = 1
and y = 2
or z = 3
go
select * from mytable
where x = 1
and ( y = 2 or z = 3)
go
drop table MyTable
October 10, 2007 at 8:18 am
lol...tell me what you really think. the 'or's' apply to the conditions within the bracketed statement, not to the whole where clause. except for the npa <> '705 north' that applies to the whole where clause.
October 10, 2007 at 11:03 am
Just try this.
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' or LEFT(custphoneno, 4) = ':269H')
and npa <> '705 North'
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' or LEFT(custphoneno, 4) = ':269H')
and npa <> '705 North'
)
Prasad Bhogadi
www.inforaise.com
October 10, 2007 at 11:20 am
Marcus Farrugia (10/10/2007)
lol...tell me what you really think. the 'or's' apply to the conditions within the bracketed statement, not to the whole where clause. except for the npa <> '705 north' that applies to the whole where clause.
The point Steve was getting at is that you have two separate instances where the or's aren't bracketed, so that allow a bypass of all of the AND's above it.
For example - the last line in your subquery. Should it be:
and (npa <> '705 North' or LEFT(custphoneno, 4) = ':269H')
OR
and npa <> '705 North' or LEFT(custphoneno, 4) = ':269H'
Option #2 (what you're currently using) just means the ONLY condition needed for a row to qualify is the LEFT(custphoneno, 4) = ':269H', never mind anything else in the WHERE clause.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2007 at 8:28 am
Thank you Steve Jones, Prasad, and Matt. I finally get what you were trying to tell me but I didn't really get it until Prasad spelled the code out for me and I was able to compare it to what I wrote but I finally did get the concept you were all trying to hammer home to me. So thank you it worked for me.
October 11, 2007 at 8:34 pm
I'd like to know why the hell you have letters in the NPA column. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 6:25 am
It's used when there isn't enough wiring to assign a phone number and is being held (h) while underground wiring is is being installed.
October 12, 2007 at 7:25 am
Heh... thanks, Marcus. Most folks keep that type of info in two separate columns...
So, are you all set or do you still need help?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2007 at 7:29 am
No I was able to figure out that the problem was stemming from my misused or's but thank you for your help.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply