Solved

not exists - help with multiple WHERE

Posted on 2014-12-18
14
95 Views
Last Modified: 2014-12-18
Hi
I have the following sql

Select		a.dGPSDateTime 
                        ,a.vSequenceID
From	(select		top 5
		dGPSDateTime
	,	vSequenceID
from	tblCommonTrackingData
where	vReportID = 8
AND      CAST(vpkDeviceID as bigint)= 862193020453528
and		dGPSDateTime >= '2014-12-17 17:06:33.000'

--and	not exists
--		(select vpkDeviceID
--		from	tblTrip_Master
--		where	nEndEventInst = 913565)

order by dGPSDateTime) as a

Open in new window


This works, and returns the following
resut

I would like to un-comment the NOT EXISTS above to exclude the selected record
tblCommonTrackingData.vSequenceID is the same as
tblTrip_Master.nEndEventInst

However, when i uncomment it, i get no results at all
Im not sure NOT EXISTS is the best fit for this

basically, i dont want the row with 913565 to appear:
Where vSequenceID isn't 913565

Open in new window


However, they are 2 different tables so not sure why it isn't working
0
Comment
Question by:websss
  • 6
  • 4
  • 4
14 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40506530
However, when i uncomment it, i get no results at all
(..)basically, i dont want the row with 913565 to appear:
Well, if with the original query you get only 2 records and both with vSequenceID = 913565 it's natural that if you don't want it to appear then no records will be returned.

Anyway, you can add the criteria vSequenceID <> 913565 to the WHERE clause:
Select	a.dGPSDateTime,a.vSequenceID
From (select top 5
		dGPSDateTime,vSequenceID
	from tblCommonTrackingData
	where	vReportID = 8
	AND     CAST(vpkDeviceID as bigint)= 862193020453528
	and		dGPSDateTime >= '2014-12-17 17:06:33.000'
	and     vSequenceID <> 913565
	order by dGPSDateTime) as a

Open in new window

0
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 100 total points
ID: 40506538
This should work as well...
Select		a.dGPSDateTime 
                        ,a.vSequenceID
From	(select		top 5
		dGPSDateTime
	,	vSequenceID
from	tblCommonTrackingData
where	vReportID = 8
AND      CAST(vpkDeviceID as bigint)= 862193020453528
and		dGPSDateTime >= '2014-12-17 17:06:33.000'

order by dGPSDateTime) as a

where a.vSequenceID <>913565
                                  

Open in new window

i.e. adding the where clause for the specific record you want to exclude to the main SELECT statement rather than the subquery. This would mean that you got a maximum of 4 results rather than 5 though, whereas Vitor's suggestion would still get you 5.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40506542
Simon, what's the difference between your solution and mine? The location of the filter?
0
 

Author Comment

by:websss
ID: 40506544
the 2 sequence Id's are different in the image
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40506546
@Vitor: Sorry, I hadn't refreshed the page. Yes the location of the filter is in the outer query and would limit the result set to max of 4 records rather than 5.
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40506551
What didn't make sense to me was this bit...
--and      not exists
--            (select vpkDeviceID
--            from      tblTrip_Master
--            where      nEndEventInst = 913565)

because it is a different column name (nEndEventInst) that is being checked, rather than "vSequenceID", though I'm guessing that both refer to the same value?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40506552
the 2 sequence Id's are different in the image
Yes, you are right. I went to double-check. So, just try to add the filter and see if you got the expected result.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:websss
ID: 40506553
Thanks both
neither solution will work since the ID is dynamic and it needs to check if it exists in the other table

i.e.
--Where sequenceID not in
--            (select nEndEventInst
--            from      tblTrip_Master
--            where      nEndEventInst = 913565
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40506554
Yes the location of the filter is in the outer query and would limit the result set to max of 4 records rather than 5.
Simon, but then you are changing the logic of the query. If the idea is to get the TOP 5 than the filter should be inside the subselect.
0
 

Author Comment

by:websss
ID: 40506556
yes
nEndEventInst and vSequenceID are different names in both tables, but contain same value
0
 

Author Comment

by:websss
ID: 40506559
..actually, I only need TOP 1 since it should only exist once in this table
i was just using TOP 5 for testing sorry
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 400 total points
ID: 40506564
neither solution will work since the ID is dynamic
If it's dynamic you need to work with a variable. In the code you posted is a constant. We can't guess if you don't provide us all the information.

This solution returns the TOP 5  records that not exists in TripMaster table:
Select	a.dGPSDateTime,a.vSequenceID
From (select top 5
		dGPSDateTime,vSequenceID
	from tblCommonTrackingData
	where	vReportID = 8
	AND     CAST(vpkDeviceID as bigint)= 862193020453528
	and		dGPSDateTime >= '2014-12-17 17:06:33.000'
	and     not exists
             (select vpkDeviceID
             from      tblTrip_Master 
             where     tblTrip_Master.nEndEventInst = tblCommonTrackingData)
	order by dGPSDateTime) as a

Open in new window

You need to find a way to add the variable for excluding the SequenceID that you want.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40506567
Forgot to add the SequenceID in the NOT EXISTS query. Also changed it for TOP 1:
Select	a.dGPSDateTime,a.vSequenceID
From (select top 1
		dGPSDateTime,vSequenceID
	from tblCommonTrackingData
	where	vReportID = 8
	AND     CAST(vpkDeviceID as bigint)= 862193020453528
	and		dGPSDateTime >= '2014-12-17 17:06:33.000'
	and     not exists
             (select vpkDeviceID
             from      tblTrip_Master 
             where     tblTrip_Master.nEndEventInst = tblCommonTrackingData.vSequenceID)
	order by dGPSDateTime) as a

Open in new window

0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40506575
You'll always get zero rows if both vSequenceIDs/nEndEventInsts are associated with the same vpkDeviceID in tblTrip_Master.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now