Joining Databases with Left Outer Joins

[Tania Louie]

Hey All,

I really need some help here!

I currently have 3 databases that I am writing a script for to extract data. I need to join these databases together to get one extract, but I have a whole bunch of data that I need and from different tables in the database. Therefore I have left outer joins to join up tables in there.

SQL won't let me join the databases if I have left outer joins as well, but I have only been trying a Union.

Any suggestions are welcome :)

[yarbrough40]

more specifics please.

what have you tryed so far? what do you mean --> "SQL won't let me join the databases if I have left outer joins"

what kind of errors are you getting?

present some examples.

[Tania Louie]

Sorry - I'm only learning SQL at the moment so I might not have it right, but this is an example:

use databasea
go

select
employee.employ eecode,
employee.firstn ames,
employee.lastna me,
employee.locati oncode,
location.descri ption,
employee.positi oncode,
position.descri ption

from employee
left outer join location location on
(employee.locat ioncode = location.locati oncode)
left outer join position position on
(employee.posit ioncode = position.positi oncode)

where employee.termin ationdate is NULL

I have written a script to join the databases using a union before, but if I try this and use a left outer join it doesn't work:

select
ea.employeecode ,
ea.firstnames,
ea.lastname,
ea.departmentco de,
ea.locationcode ,
ea.Xdatabase

from databasea.dbo.E mployee ea

where ea.terminationd ate is NULL
AND ea.EmployeeCode NOT LIKE '[_]%'

union

select
eb.employeecode ,
eb.firstnames,
eb.lastname,
eb.departmentco de,
eb.locationcode ,
eb.Xdatabase

from databaseb.dbo.E mployee eb

where eb.terminationd ate is NULL
AND eb.EmployeeCode NOT LIKE '[-]%'


Please excuse my very basic scripts, I'm just a beginner :)

[yarbrough40]

the administrators are going to ask you to put your code in code tags when you post. It's the "#" sign in the messagebox menu bar.

So, I'm still confused here. you are showing two queries. One with Left Joins and One with a Union. Which one exactly is failing? and again, what errors are you getting?

[Tania Louie]

Sorry - so I have figured out how to join the databases now, but the join isn't working on the third database although it isn't failing as the employees are there, it just has NULL in the cell. I have just written the below basic formula:

use databasea
go

select
employee.employ eecode,
employee.firstn ames,
employee.lastna me,
employee.locati oncode,
location.descri ption,
employee.positi oncode,
position.descri ption

From
(
Select
ea.employeecode ,
ea.firstnames,
ea.lastname,
ea.locationcode ,
ea.positioncode ,
ea.terminationd ate

from databasea.dbo.e mployee ea

union

select
eb.employeecode ,
eb.firstnames,
eb.lastname,
eb.locationcode ,
eb.positioncode ,
eb.terminationd ate

from databaseb.dbo.e mployee eb

union

select
ec.employeecode ,
ec.firstnames,
ec.lastname,
ec.locationcode ,
ec.positioncode ,
ec.terminationd ate

from databasec.dbo.e mployee ec

)

employee
left outer join location location on
(employee.locat ioncode = location.locati oncode)
left outer join position position on
(employee.posit ioncode = position.positi oncode)

where employee.termin ationdate is NULL

order by employee.employ eecode

The above were two different scripts, I was just trying to show what I wanted to be joined and then also how I have joined the databases with a union before.

The above script should make more sense.

Thanks

[Tania Louie]

Sorry I mean't the last reply (#3) was two different scripts.

[Tania Louie]

Ahhh... I have just realised that my databases are not exactly the same and therefore there are NULL's because the fields aren't set up in some of the databases.

Is this the best way to join them?

[yarbrough40]

So are you saying your queries are working now? If so then you are fine with the way you have them constructed.

the only thing I see is that I don't believe you need to repeat your table name

this is yours:

left outer join location location on

you only need this:

left outer join location on