Solved

not exists - help with multiple WHERE

Posted on 2014-12-18
14
98 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 50

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 50

Expert Comment

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

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 50

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 50

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 50

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 50

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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
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.

738 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