I have two tables with a 1-many relationship. I want to write a
select statement that looks in the table w/many records and compares
it to the records in the primary table to see if there are any records
that do not match based on a certain field.
Here is how my tables are setup:
Task Code Table
TCode,TCodeFName,Active
Tracking Table(multiple records)
ID,User,ProjectCode,TCode,Hours,Date
I want to look at the tracking table and see if there are any TCode
listed here that are not listed in the Task Code table.
How do I write a SQL statement to do this? 6 129993
Michael,
Try this:
select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)
Shervin
"Michael" <lu****@yahoo.com> wrote in message
news:bc*************************@posting.google.co m... I have two tables with a 1-many relationship. I want to write a select statement that looks in the table w/many records and compares it to the records in the primary table to see if there are any records that do not match based on a certain field.
Here is how my tables are setup:
Task Code Table TCode,TCodeFName,Active
Tracking Table(multiple records) ID,User,ProjectCode,TCode,Hours,Date
I want to look at the tracking table and see if there are any TCode listed here that are not listed in the Task Code table.
How do I write a SQL statement to do this?
This is usually faster by the way, I use it a lot:
SELECT Tracking.TCode FROM TaskCode RIGHT OUTER JOIN
Tracking ON TaskCode.TCode = Tracking.TCode
WHERE TaskCode.TCode IS NULL
Cheers,
Bas
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message
news:vp************@corp.supernews.com... Michael,
Try this:
select TCode from Tracking where not exists (select * from Task where Task.TCode = Tracking.TCode )
Shervin
"Michael" <lu****@yahoo.com> wrote in message news:bc*************************@posting.google.co m... I have two tables with a 1-many relationship. I want to write a select statement that looks in the table w/many records and compares it to the records in the primary table to see if there are any records that do not match based on a certain field.
Here is how my tables are setup:
Task Code Table TCode,TCodeFName,Active
Tracking Table(multiple records) ID,User,ProjectCode,TCode,Hours,Date
I want to look at the tracking table and see if there are any TCode listed here that are not listed in the Task Code table.
How do I write a SQL statement to do this?
By the way, it's best to not use reserved words like ID,User and Date as
column names :)
Bas
"Michael" <lu****@yahoo.com> wrote in message
news:bc*************************@posting.google.co m... I have two tables with a 1-many relationship. I want to write a select statement that looks in the table w/many records and compares it to the records in the primary table to see if there are any records that do not match based on a certain field.
Here is how my tables are setup:
Task Code Table TCode,TCodeFName,Active
Tracking Table(multiple records) ID,User,ProjectCode,TCode,Hours,Date
I want to look at the tracking table and see if there are any TCode listed here that are not listed in the Task Code table.
How do I write a SQL statement to do this?
Bas,
It's interesting! I tested these three queries on two big tables:
select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)
select TCode
from Tracking
where TCode not in (select TCode
from Task
)
select Tracking.TCode
from Tracking left outer join Task on Task.TCode = Tracking.TCode
where Task.TCode is null
Then I checked the execution plan for all of them. The first two queries
took 32.87% and the last one took 34.25% of execution time of whole batch.
Both tables have indexes on TCode field. The third query has an extra step
in it which is filtering the result set and eliminating records in which
Task.TCode is null.
As you see it's not a huge difference between results. I guess it completely
depends on how you setup your indexes. But still it seems to me that using
EXISTS should be faster. :-)
Is it possible to check it on your problem and compare the results? I'm
interested to know what makes the third query faster in your case.
Thanks,
Shervin
"Bas" <nomailplease> wrote in message
news:3f***********************@dreader4.news.xs4al l.nl... This is usually faster by the way, I use it a lot:
SELECT Tracking.TCode FROM TaskCode RIGHT OUTER JOIN Tracking ON TaskCode.TCode = Tracking.TCode WHERE TaskCode.TCode IS NULL
Cheers,
Bas
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message news:vp************@corp.supernews.com... Michael,
Try this:
select TCode from Tracking where not exists (select * from Task where Task.TCode = Tracking.TCode )
Shervin
"Michael" <lu****@yahoo.com> wrote in message news:bc*************************@posting.google.co m... I have two tables with a 1-many relationship. I want to write a select statement that looks in the table w/many records and compares it to the records in the primary table to see if there are any records that do not match based on a certain field.
Here is how my tables are setup:
Task Code Table TCode,TCodeFName,Active
Tracking Table(multiple records) ID,User,ProjectCode,TCode,Hours,Date
I want to look at the tracking table and see if there are any TCode listed here that are not listed in the Task Code table.
How do I write a SQL statement to do this?
Bas (nomailplease) writes: This is usually faster by the way, I use it a lot:
SELECT Tracking.TCode FROM TaskCode RIGHT OUTER JOIN Tracking ON TaskCode.TCode = Tracking.TCode WHERE TaskCode.TCode IS NULL
Maybe it was in SQL 6.5. I would not expect so in SQL 2000. These two
queries have very similar query plans:
select * from Northwind..Customers c
where not exists (select * from
Northwind..Orders o where o.CustomerID = c.CustomerID)
select c.*
FROM Northwind..Customers c
LEFT JOIN Northwind..Orders o on c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL
But the latter has a extra filter step the first query don't need.
I advice against using outer joins for NOT EXISTS queries. NOT EXISTS
more clearly expresses what you are after. Another advantage to use
NOT EXISTS is that does require you to know what is LEFT and RIGHT.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Hmm funny!
Must have been som MS Access knowledge from long ago that got stuck with me.
I checked, Access uses the technique itself in one of its wizards to find
unmatched records.
Thanks for pointing this out.
Bas
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message
news:vp************@corp.supernews.com... Bas,
It's interesting! I tested these three queries on two big tables:
select TCode from Tracking where not exists (select * from Task where Task.TCode = Tracking.TCode )
select TCode from Tracking where TCode not in (select TCode from Task )
select Tracking.TCode from Tracking left outer join Task on Task.TCode = Tracking.TCode where Task.TCode is null
Then I checked the execution plan for all of them. The first two queries took 32.87% and the last one took 34.25% of execution time of whole batch. Both tables have indexes on TCode field. The third query has an extra step in it which is filtering the result set and eliminating records in which Task.TCode is null. As you see it's not a huge difference between results. I guess it
completely depends on how you setup your indexes. But still it seems to me that using EXISTS should be faster. :-) Is it possible to check it on your problem and compare the results? I'm interested to know what makes the third query faster in your case.
Thanks, Shervin
"Bas" <nomailplease> wrote in message news:3f***********************@dreader4.news.xs4al l.nl... This is usually faster by the way, I use it a lot:
SELECT Tracking.TCode FROM TaskCode RIGHT OUTER JOIN Tracking ON TaskCode.TCode = Tracking.TCode WHERE TaskCode.TCode IS NULL
Cheers,
Bas
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message news:vp************@corp.supernews.com... Michael,
Try this:
select TCode from Tracking where not exists (select * from Task where Task.TCode = Tracking.TCode )
Shervin
"Michael" <lu****@yahoo.com> wrote in message news:bc*************************@posting.google.co m... > I have two tables with a 1-many relationship. I want to write a > select statement that looks in the table w/many records and compares > it to the records in the primary table to see if there are any
records > that do not match based on a certain field. > > Here is how my tables are setup: > > Task Code Table > TCode,TCodeFName,Active > > Tracking Table(multiple records) > ID,User,ProjectCode,TCode,Hours,Date > > I want to look at the tracking table and see if there are any TCode > listed here that are not listed in the Task Code table. > > How do I write a SQL statement to do this?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: margraft |
last post by:
Hello,
I'm somewhat of a newbie and I need to create a view with a column
that is not derived from any other tables. What I want is for this
field to be an auto-increment field or some kind of...
|
by: BuddhaBuddy |
last post by:
Platform is DB2/NT 7.2.9
The table was created like this:
CREATE TABLE MYTEST (
MYTESTOID bigint not null primary key,
FK_OTHEROID bigint not null references other,
FK_ANOTHEROID bigint not...
|
by: bitoulis |
last post by:
Hi,
is it possible to use the records of a table as the field names of
another table?
If yes, how is it done?
Thanks in advance
Laertes
|
by: rcamarda |
last post by:
I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I...
|
by: bpforte |
last post by:
Hello,
I need help with building query, basically I need to select all records
from one table that don't exists in second table with status 1, but
they can exists in second table with status 0, to...
|
by: Lennart |
last post by:
I really like the construction:
select * from new table (update ....) X
but I noticed that it cant be used as:
insert into T select * from new table (update ....) X
because of:
|
by: paulcrowsnest |
last post by:
Hi everyone
This is probably really simple, but it has me stumped.
I have a form which gets its data from a table called "Dentistry", this table is linked to another table called...
|
by: =?Utf-8?B?S3VydCBvZiBTYW4gSm9zZQ==?= |
last post by:
All-
Please advise as to what's the best way to display data in a column in a
grid view (and I'm using a dataset) in a table (say TABLE2) which:
- is non-zero only if a boolian field in the...
|
by: dannix |
last post by:
Hi,
I hope I can explain this right:
Here is my statement so far:
SELECT shift_log.note, shift_log.id, shift_log.timestamp, shift_log.monitor, shift_log.handover,
shift_log.start_time,...
|
by: Rina0 |
last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |