gathering data from two tables in separate databases

[sspeedy00]

Hi all,

I'm new here and this is my 2nd post. I already got tremendous help and hope someone can help me as I learn sql (took mysql two years ago). I am REALLY rusty, so I appreciate any tips/help.

Database 1:
table 1. Contains ssn, id.

Database 2:
table 2. Contains ssn, id, last_name, first_name.

So, here's what I need to do. I need to search in db1.dbo.table1 for id = 0; this is how I do it:

select x.ssn from db1.dbo.table1 x
where x.emplid = '0'
order by s.ssn asc

Now... I have all the SSN's where id = 0. However, I want to display the results by last_name and first_name, not SSN. To retrieve the names, I have to search table 2, db2.

I'm really stuck here and as far as I know, this is probably pretty easy to do. Is there a way I can store the ssn's I pull from the first table, then do something like this:

select y.last_name, y.first_name
from database2.dbo.table2 y
where stored_ssn = y.ssn

given that stored_ssn is a variable that stores all the ssn query results from table 1.

I hope this doesn't sound overly complicated. I need to pull the names from table 2 based on the ssn I have pulled from table 1.

TIA.

 

[gmmastros]

There shouldn't be any need to 'store' the ssn's.

Select y.last_name, y.first_name
From   database2.dbo.table2 y
       Inner Join (
         Select x.ssn from db1.dbo.table1 x
         where x.emplid = '0'
         ) As X
         On y.ssn = x.ssn
Order By last_name, first_name

Notice the Parenthesis ( ) in the 'Inner Join ' part. This method is called a [google]derived table[/google] (I recommend a little research on this).

Alternatively, you could simply join the queries like this.

Select y.last_name, y.first_name
From   database2.dbo.table2 y
       Inner Join db1.dbo.table1 x
         On y.ssn = x.ssn
Where  X.emplid = '0'
Order By last_name, first_name

-George

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

 

[SQLSister]

This might help you start to understand joining tables:

http://www.somecoders.com/2006/02/explanation-of-the-different-sql-joins/

"NOTHING is more important in a database than integrity." ESquared

 

[sspeedy00]

SQLSister, thanks for the link. I will be playing around with all these join commands

gmmastros--I tried your first suggestion, i.e.:

Select y.last_name, y.first_name
From   database2.dbo.table2 y
       Inner Join (
         Select x.ssn from db1.dbo.table1 x
         where x.emplid = '0'
         ) As X
         On y.ssn = x.ssn
Order By last_name, first_name

I changed the last line to 'order by y.ssn'. I get 27 results.

When I go back to my simple query:

select s.ssn from ReportedTime.dbo.tblScanRecord s 
where s.emplid = '0' 
order by s.ssn asc

it results in 60 rows.

Comparing the results (first 25 or so), the first query eats a few of the results. I can't explain it, but perhaps somehow this line "On y.ssn = x.ssn" is somehow restricting some of the rows I see in my 2nd simple query.

I really want to understand this and appreciate your help, gm, and looking forward to your reply.

 

[gmmastros]

Since you are using inner join, you will only get records where the ssn matches in both tables. If you feel confident that there should be more, then I would suggest that there might be some slight differences in the data that are difficult to notice. For example, if there is a space before the ssn, then it won't match. There could also be 'hidden' characters like Tab, Carriage Return, Line Feed, etc...

Of course, if the records don't exist in the other table, then that would certainly explain it too.



-George

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

 

[sspeedy00]

gm,
you were correct. I tweaked your suggestion and am now using a left outer join which gives me the correct data set:

Select y.last_name, y.first_name
From   database2.dbo.table2 y
       Left Outer Join db1.dbo.table1 x
         On y.ssn = x.ssn
Where  X.emplid = '0'
Order By last_name, first_name

thanks muchos. have a great day!