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.employeecode,
employee.firstnames,
employee.lastname,
employee.locationcode,
location.description,
employee.positioncode,
position.description

from employee
left outer join location location on
(employee.locationcode = location.locationcode)
left outer join position position on
(employee.positioncode = position.positioncode)

where employee.terminationdate 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.departmentcode,
ea.locationcode,
ea.Xdatabase

from databasea.dbo.Employee ea

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

union

select
eb.employeecode,
eb.firstnames,
eb.lastname,
eb.departmentcode,
eb.locationcode,
eb.Xdatabase

from databaseb.dbo.Employee eb

where eb.terminationdate 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.employeecode,
employee.firstnames,
employee.lastname,
employee.locationcode,
location.description,
employee.positioncode,
position.description

From
(
Select
ea.employeecode,
ea.firstnames,
ea.lastname,
ea.locationcode,
ea.positioncode,
ea.terminationdate

from databasea.dbo.employee ea

union

select
eb.employeecode,
eb.firstnames,
eb.lastname,
eb.locationcode,
eb.positioncode,
eb.terminationdate

from databaseb.dbo.employee eb

union

select
ec.employeecode,
ec.firstnames,
ec.lastname,
ec.locationcode,
ec.positioncode,
ec.terminationdate

from databasec.dbo.employee ec

)

employee
left outer join location location on
(employee.locationcode = location.locationcode)
left outer join position position on
(employee.positioncode = position.positioncode)

where employee.terminationdate is NULL

order by employee.employeecode

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