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 :)
7 1598
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.
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 :)
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?
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
Sorry I mean't the last reply (#3) was two different scripts.
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?
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
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Dam |
last post by:
Using SqlServer :
Query 1 :
SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,
serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeur
where def.TYPEDETABLEDECODES = 4
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins" slows the system down considerably.
I've tried creating a temp db, but I can't figure out how to execute
two select commands. (It throws the exception "The column prefix
'tempdb' does not match with a table name or alias name used in the
query.")
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins" slows the system down considerably.
I've tried creating a temp db, but I can't figure out how to execute
two select commands. (It throws the exception "The column prefix
'tempdb' does not match with a table name or alias name used in the
query.")
|
by: Ian Boyd |
last post by:
i know nothing about DB2, but i'm sure this must be possible.
i'm trying to get a client to create a view (which it turns out is called a
"Logical" in DB2). The query needs a LEFT OUTER JOIN, but he doesn't know
how to do that, or even if he can, and i don't have to time to learn DB2
from scratch right now.
The following SQL Query is a trimmed sample of the full View (i.e. Logical)
definition - and i would create it on an SQL based...
|
by: Dev |
last post by:
Hello all,
I need to do a left out join where a.field1 ilike %b.field2%
But I can not figure out the exact syntax to using the ilike in the join?
----------
| |
by: Brian Parker |
last post by:
I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working
on a three table query.
Assumptions:
-- I have events in the Event table.
-- Each event CAN have one Transaction, but it's not guaranteed
-- Each transaction, ir present, will have one or more Amount records
This would be the pseudo-query without any special joins:
|
by: media.opslag |
last post by:
Hi,
How can i get this to work in access / jet sql ??? Someone??
SELECT
tbl1.,
tbl2.
FROM tbl1
left outer join tbl2 on
|
by: ibadba |
last post by:
Hello all, today I think I'm stupid, can anyone confirm??
I have a query on multinode DB2 ( v8.2 Aix ), as follows:
select a.col1,b.col1,c.col1
from
table1 a
left outer join
table2 b
on
|
by: Mickey001 |
last post by:
Greetings all,
I need some help in writing and understanding a sql query for the following:
Here is a simplified scenario:
2 Tables:
First Table Name
=============
|
by: nico3334 |
last post by:
I currently have a query that Joins 2 Tables (Table1 and Table2) using
LEFT OUTER JOIN. Here is an example of that query:
SELECT a.supply,
a.state,
b.cost
FROM Table1 a
LEFT OUTER JOIN Table2 b
ON a.supply = b.supply
AND a.state = b.state
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |