selecting data from table1 column that doesn't exist in table 2column

[sspeedy00]

Hello Everyone,

First post here. I've taken a databases course (mysql) about 2 years ago and now I'm using MSSQL and having trouble recalling all that I had learned.

I'm trying to do something fairly simple here. Let me explain:

Database 1:
Contains a table with column SSN.

Database 2:
Contains a table with column SSN.

Now, database 1 might have much more ssn entries. i.e. say, database 1, table contains 50 ssn entries. database 2 contains 30.

I want a query that shows you the difference (the 20 ssn's that are not in database 2).

Yes, that's all I want.

select database1.dbo.table1.ssn --probably incorrect syntax
from database1.dbo.table1, database2,dbo.table1
where ??????

Also, this is my first visit here. Feel free to point me to a good tutorial/review articles.

TIA.

 

[gmmastros]

For questions regarding Microsoft SQL Server, you should post here: forum183

select table1.ssn
from   table1
       Left Join table2
         On Table1.ssn = Table2.ssn
where  Table2.ssn Is NULL

Do a little research on left join. If you have questions about this query, let me know and I will explain it for you.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sspeedy00]

Hello gmmastros,
thanks so much for the help. I am now getting the results that I need. I did a bit of a lookup on left join, and here's a clear explanation I found:

"The LEFT JOIN operation is used to create a left outer join and includes all of the records from the first (left) of the two tables, even if there are no matching values for records in the second."

So now I understand the fact that a left join will return all the ssn data from the first table, and the right join will return all the ssn data from the second table.

I'm not exactly sure about the very last where line you posted:
"where table2.id is NULL"

If I comment that out, I get all the ssn data from table1. If I leave it as NULL, then it returns the difference data from table1. Can you please provide an explanation of this where clause?

TIA.

 

[gmmastros]

Sure.

With a left join, you get all records from the left table. If there is a matching record in the right table, you get that too. If there is no matching record in the right table, you still get the columns, but every value will be NULL.

For educational purposes, change your query to...

select table1.ssn, Table2.ssn
from   table1
       Left Join table2
         On Table1.ssn = Table2.ssn

You will get a row (in the output) for each row in Table1. If a record matches in Table2, then the Table2.ssn column will have a value. If there is no match, the value for table2.ssn will be NULL.

It doesn't really make sense to return both columns, so in your final version, you'll only want table1.ssn. However, we can use the fact that NULLs are returned to filter out the records that do have matches. This is where the WHERE clause comes in to play. By filtering on Table2.ssn is NULL, we will return the table1 ssn's where there is no match in table 2. This is what you wanted isn't it.

Does this make sense now? If not, let me know and I will explain more.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sspeedy00]

gmmastros,
makes perfect sense now. I might run into more problems later (when I start developing this project). Guess I need to read up on dbs again and constructing queries with the appropriate.... 'commands' I suppose.

have a great day, gm!

 

[gmmastros]

>> I might run into more problems later

Who doesn't?

If you feel the need to post another question, that's fine. I do encourage you to post your question in the Microsoft SQL Server forum (forum183). The ANSI_SQL guys get a little twitchy when they see Microsoft SQL Server specific questions.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom