Solved

not exists - help with multiple WHERE

Posted on 2014-12-18
14
97 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 47

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:Simon
Simon 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 47

Expert Comment

by:Vitor Montalvão
ID: 40506542
Simon, what's the difference between your solution and mine? The location of the filter?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

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

Expert Comment

by:Simon
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:Simon
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 47

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
 

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 47

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 47

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 47

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:Simon
ID: 40506575
You'll always get zero rows if both vSequenceIDs/nEndEventInsts are associated with the same vpkDeviceID in tblTrip_Master.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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