473,889 Members | 1,350 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Statement select from one table where not in another table

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,TCodeFNam e,Active

Tracking Table(multiple records)
ID,User,Project Code,TCode,Hour s,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?
Jul 20 '05 #1
6 130057
Michael,

Try this:

select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)

Shervin

"Michael" <lu****@yahoo.c om> wrote in message
news:bc******** *************** **@posting.goog le.com...
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,TCodeFNam e,Active

Tracking Table(multiple records)
ID,User,Project Code,TCode,Hour s,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?

Jul 20 '05 #2
Bas
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**********@h otmail.com> wrote in message
news:vp******** ****@corp.super news.com...
Michael,

Try this:

select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)

Shervin

"Michael" <lu****@yahoo.c om> wrote in message
news:bc******** *************** **@posting.goog le.com...
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,TCodeFNam e,Active

Tracking Table(multiple records)
ID,User,Project Code,TCode,Hour s,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?


Jul 20 '05 #3
Bas
By the way, it's best to not use reserved words like ID,User and Date as
column names :)

Bas

"Michael" <lu****@yahoo.c om> wrote in message
news:bc******** *************** **@posting.goog le.com...
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,TCodeFNam e,Active

Tracking Table(multiple records)
ID,User,Project Code,TCode,Hour s,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?

Jul 20 '05 #4
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. xs4all.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**********@h otmail.com> wrote in message
news:vp******** ****@corp.super news.com...
Michael,

Try this:

select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)

Shervin

"Michael" <lu****@yahoo.c om> wrote in message
news:bc******** *************** **@posting.goog le.com...
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,TCodeFNam e,Active

Tracking Table(multiple records)
ID,User,Project Code,TCode,Hour s,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?



Jul 20 '05 #5
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..Cust omers c
where not exists (select * from
Northwind..Orde rs o where o.CustomerID = c.CustomerID)

select c.*
FROM Northwind..Cust omers c
LEFT JOIN Northwind..Orde rs 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
Jul 20 '05 #6
Bas
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**********@h otmail.com> wrote in message
news:vp******** ****@corp.super news.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. xs4all.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**********@h otmail.com> wrote in message
news:vp******** ****@corp.super news.com...
Michael,

Try this:

select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)

Shervin

"Michael" <lu****@yahoo.c om> wrote in message
news:bc******** *************** **@posting.goog le.com...
> 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,TCodeFNam e,Active
>
> Tracking Table(multiple records)
> ID,User,Project Code,TCode,Hour s,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?



Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1452
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 UID. Can anyone quickly tell me how to do this. Here is my dilemma. I need to pull data from a unique data set from a table which does not have a primary key, and none exists in its data. Please tell me how to put this data in a view(or...
10
26151
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 null references another, FK_LASTLYOID bigint not null references lastly, unique (FK_OTHEROID,FK_ANOTHEROID))
3
2483
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
7
8399
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 need a new column that is the days between the date and the MID_DT The data I wish to end with would look something like this: PERIOD DATE DAY_NO 200602 2005-07-06 -89 200602 2005-07-07 -88 200602 2005-07-08 -87
1
5847
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 be more complicated there can be in the same record in second table with status 0 and 1 (second table is something like log) in oracle I can do: select record from table_1 where record is not in (select record from table_2 where status=1)
5
7615
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:
4
6621
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 "SurgeryInventory"(this table has a list of various Surgery procedures and there related cost value for each procedure). Each Procedure in the "SurgeryInventory" Table is assigned a CategoryID so i can easily filter the combo's in the form to only list the...
0
1879
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 same row is true, and - when the boolean field is true, gets its value FROM ANOTHER TABLE? Say we have these two tables: TABLE1
1
2816
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, shift_log.finish_time, shift_log.sunref, shift_log.hpref, shift_log.hsbcref, CONCAT(users.fname, " ", users.lname) AS users_name, equipment.name AS equipment_name, equipment_id"; FROM shift_log, equipment, users, part_usage
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9807
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10789
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10441
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9606
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5828
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6028
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4647
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.