Nested Queries Help

13 Jul 2006 1:47 PM

shil

Hi,

Seems like very simple but I couldn't figure out why I'm having issues
with nested queries.

I have two tables T1 and T2 in which there is a common field which is
SoldTo. Here T1 is a master table from which I insert data into T2 if
there are any new SoldTo's in T1. For which,
I wrote a query to get all SoldTo values that doens't exist in T2. I
know there are few SoldTo values in T1 that are not in T2. When I
searched for individual SoldTo values, I found them.
But when I execute my query below, I get zero results.

Here is my tables structure and query

T1                                                 T2
________________________________________________________
ID(PrimaryKey)                               FacilityID(PrimaryKey)
SoldToCode(nvarchar(50))                SoldTo(nvarchar(50))
SoldToName(nvarchar(100))             FacilityName(nvarchar(100))
City(nvarchar(50))
State(nvarchar(50))
Country(nvarchar(50))

There are some more fields in Table T2

SELECT * FROM T1 WHERE SoldToCode NOT IN (SELECT SoldTo FROM T2)

Can any one help me to write a right query.

13 Jul 2006 1:53 PM

SQL Menace

Probably NULL values

SELECT * FROM T1 WHERE SoldToCode NOT IN (SELECT SoldTo FROM T2 WHERE
SoldTo IS NOT NULL)

or better

SELECT * FROM T1 WHERE NOT EXISTS (SELECT * FROM T2 WHERE SoldTo =
SoldToCode)

you can also do a LEFT JOIN

Denis the SQL Menace
http://sqlservercode.blogspot.com/

shil wrote:
Show quote

> Hi,
>
> Seems like very simple but I couldn't figure out why I'm having issues
> with nested queries.
>
> I have two tables T1 and T2 in which there is a common field which is
> SoldTo. Here T1 is a master table from which I insert data into T2 if
> there are any new SoldTo's in T1. For which,
> I wrote a query to get all SoldTo values that doens't exist in T2. I
> know there are few SoldTo values in T1 that are not in T2. When I
> searched for individual SoldTo values, I found them.
> But when I execute my query below, I get zero results.
>
> Here is my tables structure and query
>
> T1                                                 T2
> ________________________________________________________
> ID(PrimaryKey)                               FacilityID(PrimaryKey)
> SoldToCode(nvarchar(50))                SoldTo(nvarchar(50))
> SoldToName(nvarchar(100))             FacilityName(nvarchar(100))
> City(nvarchar(50))
> State(nvarchar(50))
> Country(nvarchar(50))
>
> There are some more fields in Table T2
>
> SELECT * FROM T1 WHERE SoldToCode NOT IN (SELECT SoldTo FROM T2)
>
> Can any one help me to write a right query.

13 Jul 2006 3:02 PM

Arnie Rowland

SELECT *
FROM T1
LEFT JOIN T2
ON T1.SoldToCode = T2.SoldTo
WHERE T2.SoldTo IS NULL

--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."

Show quote

"shil" <joshi***@gmail.com> wrote in message news:1152798448.567870.298580@75g2000cwc.googlegroups.com...
> Hi,
>
> Seems like very simple but I couldn't figure out why I'm having issues
> with nested queries.
>
> I have two tables T1 and T2 in which there is a common field which is
> SoldTo. Here T1 is a master table from which I insert data into T2 if
> there are any new SoldTo's in T1. For which,
> I wrote a query to get all SoldTo values that doens't exist in T2. I
> know there are few SoldTo values in T1 that are not in T2. When I
> searched for individual SoldTo values, I found them.
> But when I execute my query below, I get zero results.
>
> Here is my tables structure and query
>
> T1                                                 T2
> ________________________________________________________
> ID(PrimaryKey)                               FacilityID(PrimaryKey)
> SoldToCode(nvarchar(50))                SoldTo(nvarchar(50))
> SoldToName(nvarchar(100))             FacilityName(nvarchar(100))
> City(nvarchar(50))
> State(nvarchar(50))
> Country(nvarchar(50))
>
> There are some more fields in Table T2
>
> SELECT * FROM T1 WHERE SoldToCode NOT IN (SELECT SoldTo FROM T2)
>
> Can any one help me to write a right query.
>

13 Jul 2006 3:25 PM

shil

Thank you very much guys.

I never thought about NULL values. The culprit was NULL in my T2 table.
Again, I appreciate your help. I wasted a day on this issue.