NOT IN Keyword

[wasimhc]

Hi
Pls read the followings and help me.

Table "a" has the following data:

Table a:
========
-------------------
Col1 | Col2 |
-------------------
A1 | B1 |
-------------------
A2 | B2 |
-------------------
A3 | B3 |
-------------------

And table "b" has two column: Col1 and Col2, but has no data.

However, Pls read the following statement:
SELECT a.Col1, a.Col2 FROM a, b
WHERE a.Col1 = b.Col2
AND a.Col1 NOT IN ( SELECT Col1 FROM b)

If I execute this SQL statement, no record is retrived. But, I need to retrive three record and it should be happened.

On the other hand, if table "b" has a record as follows:

Table b:
========
-------------------
Col1 | Col2 |
-------------------
A3 | B3 |
-------------------


I can retrive two records by executing that SQL statement. In this case, there is no problem.

What should i do when table "b" has no data?

Waiting for your answer,
Thanks.
Wasim

 

[Transcend]

SELECT a.Col1, a.Col2 FROM a, b
WHERE a.Col1 = b.Col2
AND a.Col1 NOT IN ( SELECT Col1 FROM b)


You have a.Col1 = b.Col2 in your where statement, if there is no data in table b then this statement will never return any values.

Try this:

SELECT a.Col1, a.Col2 FROM a, b
WHERE a.Col1 NOT IN ( SELECT Col1 FROM b)


Transcend

 

[wasimhc]

Thans for your quick response.

There is same problem.

 

[SimonA]

Try this:

SELECT a.Col1, a.Col2 FROM a
WHERE a.Col1 NOT IN ( SELECT Col1 FROM b)

 

[wasimhc]

Thanks. It is OK. I want to accept your comment as answer.

 

[tlbroadbent]

You specified an INNER JOIN which means only the rows that meet the JOIN criteria will be returned. When table b contains no rows, no rows meet the criteria so no rows are returned. Use a LEFT JOIN as follows.

SELECT a.Col1, a.Col2
FROM a
LEFT JOIN b
WHERE a.Col1 = b.Col2
AND Not Exists (SELECT * FROM b WHERE col1=a.col1) If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm