Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

not exists - help with multiple WHERE

Posted on 2014-12-18
14
Medium Priority
?
101 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 52

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 400 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 52

Expert Comment

by:Vitor Montalvão
ID: 40506542
Simon, what's the difference between your solution and mine? The location of the filter?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 52

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 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 1600 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 52

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 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