Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding another db to a view

Status
Not open for further replies.
SheepDog

SheepDog

Programmer
Feb 4, 2003
232
US
I have the view listed below and want to add a table from another data base to it. The data base is Institute and the table is Prospect_Applicant.

SELECT DISTINCT
dbo.PEOPLE.PEOPLE_ID, dbo.PEOPLE.FIRST_NAME, dbo.PEOPLE.LAST_NAME, dbo.ACADEMIC.ACADEMIC_YEAR, dbo.ACADEMIC.ACADEMIC_TERM,
dbo.ADDRESS.ADDRESS_LINE_1, dbo.ADDRESS.STATE, dbo.ADDRESS.CITY, dbo.ADDRESS.ZIP_CODE, dbo.ADDRESS.DAY_PHONE,
dbo.ADDRESS.EVENING_PHONE, dbo.ADDRESS.EMAIL_ADDRESS, dbo.volunteer_interest.people_code_id,
dbo.volunteer_interest.last_name AS Recruiter_LName, dbo.volunteer_interest.first_name AS Recruiter_FName,
dbo.volunteer_interest.address_line_1 AS RecruiterAdd, dbo.volunteer_interest.city AS Recruiter_City, dbo.volunteer_interest.state AS Recruiter_State,
dbo.volunteer_interest.zip_code AS Recruiter_Zip_Code, dbo.volunteer_interest.email_address AS Recruiter_Email,
dbo.ACADEMIC.APP_DECISION
FROM dbo.PEOPLE LEFT OUTER JOIN
dbo.ADDRESS ON dbo.PEOPLE.PREFERRED_ADD = dbo.ADDRESS.ADDRESS_TYPE AND
dbo.PEOPLE.PEOPLE_CODE_ID = dbo.ADDRESS.PEOPLE_ORG_CODE_ID LEFT OUTER JOIN
dbo.ACADEMIC ON dbo.PEOPLE.PEOPLE_CODE_ID = dbo.ACADEMIC.PEOPLE_CODE_ID LEFT OUTER JOIN
Institute.dbo.SemesterInfo Sem ON dbo.ACADEMIC.ACADEMIC_YEAR = Sem.academic_year RIGHT OUTER JOIN
dbo.volunteer_interest ON dbo.ADDRESS.CITY = dbo.volunteer_interest.city AND dbo.ADDRESS.STATE = dbo.volunteer_interest.state
WHERE (dbo.ACADEMIC.ACADEMIC_FLAG = 'n') AND (dbo.ACADEMIC.APPLICATION_FLAG = 'y') AND (Sem.offset = '0') AND
(dbo.ACADEMIC.APP_DECISION <> 'cx') AND (dbo.ACADEMIC.APP_DECISION <> 'rej')
 
Sort by date Sort by votes
The syntax is server.database.owner.table.column

-------------------------
Sometimes the grass is greener on the other side because there is more manure there !
 
Upvote 0 Downvote
You want to add another table from another database but is there any connection between your present query and this new table...please be more clear...

as far as the query is concerned... with your new table it should look something like this...

SELECT dbo.PEOPLE.PEOPLE_ID, dbo.Institute.Prospect_Applicant
FROM dbo.PEOPLE, dbo.Institute
...and so on

you have to explicitly include database names everywhere..

-VJ
 
Upvote 0 Downvote
Join Data Base Institute, Table Prospect_Applicant, Field Recruiter_People_Code_ID to Table PEOPLE, Field PEOPLE_CODE_ID


Join Data Base Institute, Table Prospect_Applicant, Field Applicant_People_ID to Table PEOPLE, Field PEOPLE_ID
 
Upvote 0 Downvote
I want to add the table from the other data base do I have to and make joins explained in the past post.
 
Upvote 0 Downvote
It depends on your requirements whether to make a join or not..

but if you do want to do a join your query should look something like this:

OUTER JOIN dbo.Institute.Prospect_Applicant ON dbo.XXXX.People ON dbo.Institute.Prospect_Applicant.Recruiter_People_Code_ID = dbo.XXXX.PEOPLE.PEOPLE_CODE_ID

XXXX <- represents the name of the database on which you the table PEOPLE


hope this gives you some idea...

-VJ
 
Upvote 0 Downvote
Campus^ is the other db here is the code with what you sent abovae added, but am getting an error stating:Incorrect syntax near OUTER


FROM OUTER JOIN
dbo.Institute.Prospect_Applicant ON dbo.CAMPUS6.People ON
dbo.Institute.Prospect_Applicant.Recruiter_People_Code_ID = dbo.CAMPUS6.PEOPLE.PEOPLE_CODE_ID dbo.volunteer_interest RIGHT OUTER JOIN
dbo.ADDRESS ON dbo.volunteer_interest.city = dbo.ADDRESS.CITY AND dbo.volunteer_interest.state = dbo.ADDRESS.STATE RIGHT OUTER JOIN
dbo.PEOPLE ON dbo.ADDRESS.ADDRESS_TYPE = dbo.PEOPLE.PREFERRED_ADD AND
dbo.ADDRESS.PEOPLE_ORG_CODE_ID = dbo.PEOPLE.PEOPLE_CODE_ID LEFT OUTER JOIN
dbo.ACADEMIC ON dbo.PEOPLE.PEOPLE_CODE_ID = dbo.ACADEMIC.PEOPLE_CODE_ID LEFT OUTER JOIN
Institute.dbo.SemesterInfo Sem ON dbo.ACADEMIC.ACADEMIC_YEAR = Sem.academic_year
 
Upvote 0 Downvote
oops i wrote a sample query in my previous post and did not mean that you add it as it is...i said the query should look something like this...

Anyways... send me your original query and where and why do you want to add the new table....

then i or someone else can suggest you in the right direction...

-VJ
 
Upvote 0 Downvote
Here is the original query. I want recruiters that have them selected to appear in the results .
The table Prospect_Applicant from Data Base Institute is there this info is.

SELECT DISTINCT
dbo.PEOPLE.PEOPLE_ID, dbo.PEOPLE.FIRST_NAME, dbo.PEOPLE.LAST_NAME, dbo.ACADEMIC.ACADEMIC_YEAR, dbo.ACADEMIC.ACADEMIC_TERM,
dbo.ADDRESS.ADDRESS_LINE_1, dbo.ADDRESS.STATE, dbo.ADDRESS.CITY, dbo.ADDRESS.ZIP_CODE, dbo.ADDRESS.DAY_PHONE,
dbo.ADDRESS.EVENING_PHONE, dbo.ADDRESS.EMAIL_ADDRESS, dbo.volunteer_interest.people_code_id,
dbo.volunteer_interest.last_name AS Recruiter_LName, dbo.volunteer_interest.first_name AS Recruiter_FName,
dbo.volunteer_interest.address_line_1 AS RecruiterAdd, dbo.volunteer_interest.city AS Recruiter_City, dbo.volunteer_interest.state AS Recruiter_State,
dbo.volunteer_interest.zip_code AS Recruiter_Zip_Code, dbo.volunteer_interest.email_address AS Recruiter_Email,
dbo.ACADEMIC.APP_DECISION
FROM dbo.PEOPLE LEFT OUTER JOIN
dbo.ADDRESS ON dbo.PEOPLE.PREFERRED_ADD = dbo.ADDRESS.ADDRESS_TYPE AND
dbo.PEOPLE.PEOPLE_CODE_ID = dbo.ADDRESS.PEOPLE_ORG_CODE_ID LEFT OUTER JOIN
dbo.ACADEMIC ON dbo.PEOPLE.PEOPLE_CODE_ID = dbo.ACADEMIC.PEOPLE_CODE_ID LEFT OUTER JOIN
Institute.dbo.SemesterInfo Sem ON dbo.ACADEMIC.ACADEMIC_YEAR = Sem.academic_year RIGHT OUTER JOIN
dbo.volunteer_interest ON dbo.ADDRESS.CITY = dbo.volunteer_interest.city AND dbo.ADDRESS.STATE = dbo.volunteer_interest.state
WHERE (dbo.ACADEMIC.ACADEMIC_FLAG = 'n') AND (dbo.ACADEMIC.APPLICATION_FLAG = 'y') AND (Sem.offset = '0') AND
(dbo.ACADEMIC.APP_DECISION <> 'cx') AND (dbo.ACADEMIC.APP_DECISION <> 'rej')
 
Upvote 0 Downvote
I am confused with your table structures...but anyways try this and make some changes on these lines:
Code: 
SELECT DISTINCT 
dbo.CAMPUS6.PEOPLE.PEOPLE_ID, 
dbo.CAMPUS6.PEOPLE.FIRST_NAME, 
dbo.CAMPUS6.PEOPLE.LAST_NAME, 
dbo.CAMPUS6.ACADEMIC.ACADEMIC_YEAR, 
dbo.CAMPUS6.ACADEMIC.ACADEMIC_TERM, 
dbo.CAMPUS6.ADDRESS.ADDRESS_LINE_1, 
dbo.CAMPUS6.ADDRESS.STATE, 
dbo.CAMPUS6.ADDRESS.CITY, 
dbo.CAMPUS6.ADDRESS.ZIP_CODE, 
dbo.CAMPUS6.ADDRESS.DAY_PHONE, 
dbo.CAMPUS6.ADDRESS.EVENING_PHONE, 
dbo.CAMPUS6.ADDRESS.EMAIL_ADDRESS, 
dbo.CAMPUS6.volunteer_interest.people_code_id, 
dbo.CAMPUS6.volunteer_interest.last_name AS Recruiter_LName, 
dbo.CAMPUS6.volunteer_interest.first_name AS Recruiter_FName, 
dbo.CAMPUS6.volunteer_interest.address_line_1 AS RecruiterAdd,
dbo.CAMPUS6.volunteer_interest.city AS Recruiter_City,
dbo.CAMPUS6.volunteer_interest.state AS Recruiter_State, 
dbo.CAMPUS6.volunteer_interest.zip_code AS Recruiter_Zip_Code, 
dbo.CAMPUS6.volunteer_interest.email_address AS Recruiter_Email, 
dbo.CAMPUS6.ACADEMIC.APP_DECISION,
[b]dbo.Institute.Prospect_Applicant.Recruiters[/b]

FROM         

[b]dbo.Institute.Prospect_Applicant 
INNER JOIN
dbo.PEOPLE LEFT OUTER ON
dbo.Institute.Prospect_Applicant.Recruiter_People_Code_ID = dbo.CAMPUS6.PEOPLE.PEOPLE_CODE_ID[/b]
LEFT OUTER JOIN
dbo.CAMPUS6.ADDRESS ON dbo.PEOPLE.PREFERRED_ADD = dbo.ADDRESS.ADDRESS_TYPE AND 
dbo.CAMPUS6.PEOPLE.PEOPLE_CODE_ID = dbo.ADDRESS.PEOPLE_ORG_CODE_ID LEFT OUTER JOIN
dbo.CAMPUS6.ACADEMIC ON dbo.PEOPLE.PEOPLE_CODE_ID = dbo.ACADEMIC.PEOPLE_CODE_ID 
LEFT OUTER JOIN
Institute.dbo.SemesterInfo Sem ON 
dbo.CAMPUS6.ACADEMIC.ACADEMIC_YEAR = Sem.academic_year RIGHT OUTER JOIN
dbo.volunteer_interest ON dbo.ADDRESS.CITY = dbo.volunteer_interest.city AND dbo.ADDRESS.STATE = dbo.volunteer_interest.state
WHERE     
(dbo.CAMPUS6.ACADEMIC.ACADEMIC_FLAG = 'n') AND 
(dbo.CAMPUS6.ACADEMIC.APPLICATION_FLAG = 'y') AND 
(Sem.offset = '0') AND 
(dbo.CAMPUS6.ACADEMIC.APP_DECISION <> 'cx') AND 
(dbo.CAMPUS6.ACADEMIC.APP_DECISION <> 'rej')

The main thing is that if you are adding a new table fron a different database then you have to explicity state the database names...

but anyways...

what is this in your query---

Institute.dbo.SemesterInfo Sem

???

-VJ

 
Upvote 0 Downvote
When I try your sql in a new view I am getting an error
Incorrect syntax near the keyword "ON
 
Upvote 0 Downvote
In your From Statement
dbo.Institute.Prospect_Applicant
INNER JOIN
dbo.PEOPLE LEFT OUTER ON

This ON is the area of incorrect syntax
 
Upvote 0 Downvote
OK SheepDog lets start dividing the whole problem...

first let me get this straight...

Database Name: CAMPUS6
Tables under this database:
PEOPLE
ACADEMIC
ADDRESS
Volunteer_interest

Database Name: Institute
Tables under this database:
Prospect_Applicant
SemesterInfo

Did i get that straight....


Cud you show a some sample data and the kind of result you want??

-VJ
 
Upvote 0 Downvote
Your syntax is wrong when callin the external tables
Code: 
dbo.Institute.Prospect_Applicant.Recruiters
dbo.Institute.Prospect_Applicant 
dbo.Institute.Prospect_Applicant.Recruiter_People_Code_ID dbo.CAMPUS6.PEOPLE.PEOPLE_CODE_ID

it looks like you have owner.db.table.column
you need db.owner.table.column

Code: 
dbo.Institute.Prospect_Applicant.Recruiters
Institute.dbo.Prospect_Applicant.Recruiters

dbo.Institute.Prospect_Applicant.Recruiter_People_Code_ID 
Institute.dbo.Prospect_Applicant.Recruiter_People_Code_ID 


dbo.CAMPUS6.PEOPLE.PEOPLE_CODE_ID
CAMPUS6.dbo.PEOPLE.PEOPLE_CODE_ID



Shoot Me! Shoot Me NOW!!!
- Daffy Duck
 
Upvote 0 Downvote
Yes my mistake...MDXer is correct

Try this now:
Code: 
SELECT DISTINCT 
CAMPUS6.dbo.PEOPLE.PEOPLE_ID, 
CAMPUS6.dbo.PEOPLE.FIRST_NAME, 
CAMPUS6.dbo.PEOPLE.LAST_NAME, 
CAMPUS6.dbo.ACADEMIC.ACADEMIC_YEAR, 
CAMPUS6.dbo.ACADEMIC.ACADEMIC_TERM, 
CAMPUS6.dbo.ADDRESS.ADDRESS_LINE_1, 
CAMPUS6.dbo.ADDRESS.STATE, 
CAMPUS6.dbo.ADDRESS.CITY, 
CAMPUS6.dbo.ADDRESS.ZIP_CODE, 
CAMPUS6.dbo.ADDRESS.DAY_PHONE, 
CAMPUS6.dbo.ADDRESS.EVENING_PHONE, 
CAMPUS6.dbo.ADDRESS.EMAIL_ADDRESS, dbo.volunteer_interest.people_code_id, 
CAMPUS6.dbo.volunteer_interest.last_name AS Recruiter_LName, 
CAMPUS6.dbo.volunteer_interest.first_name AS Recruiter_FName, 
CAMPUS6.dbo.volunteer_interest.address_line_1 AS RecruiterAdd, 
CAMPUS6.dbo.volunteer_interest.city AS Recruiter_City, 
CAMPUS6.dbo.volunteer_interest.state AS Recruiter_State, 
CAMPUS6.dbo.volunteer_interest.zip_code AS Recruiter_Zip_Code, 
CAMPUS6.dbo.volunteer_interest.email_address AS Recruiter_Email, 
CAMPUS6.dbo.ACADEMIC.APP_DECISION
Institute.dbo.Prospect_Applicant.Recruiters

FROM
Institute.dbo.Prospect_Applicant
INNER JOIN      
CAMPUS6.dbo.PEOPLE
ON
Institute.dbo.Prospect_Applicant.Recruiter_People_Code_ID = 
CAMPUS6.dbo.PEOPLE.PEOPLE_CODE_ID
 
LEFT OUTER JOIN
CAMPUS6.dbo.ADDRESS 
ON 
CAMPUS6.dbo.PEOPLE.PREFERRED_ADD = CAMPUS6.dbo.ADDRESS.ADDRESS_TYPE 
AND 
CAMPUS6.dbo.PEOPLE.PEOPLE_CODE_ID = CAMPUS6.dbo.ADDRESS.PEOPLE_ORG_CODE_ID 
LEFT OUTER JOIN
dbo.ACADEMIC 
ON 
CAMPUS6.dbo.PEOPLE.PEOPLE_CODE_ID = CAMPUS6.dbo.ACADEMIC.PEOPLE_CODE_ID 
LEFT OUTER JOIN
Institute.dbo.SemesterInfo Sem 
ON 
CAMPUS6.dbo.ACADEMIC.ACADEMIC_YEAR = Sem.academic_year 
RIGHT OUTER JOIN
CAMPUS6.dbo.volunteer_interest 
ON 
CAMPUS6.dbo.ADDRESS.CITY = CAMPUS6.dbo.volunteer_interest.city 
AND CAMPUS6.dbo.ADDRESS.STATE = CAMPUS6.dbo.volunteer_interest.state
WHERE 
(CAMPUS6.dbo.ACADEMIC.ACADEMIC_FLAG = 'n') AND 
(CAMPUS6.dbo.ACADEMIC.APPLICATION_FLAG = 'y') AND 
(Sem.offset = '0') AND 
(CAMPUS6.dbo.ACADEMIC.APP_DECISION <> 'cx') AND 
(CAMPUS6.dbo.ACADEMIC.APP_DECISION <> 'rej')

-VJ
 
Upvote 0 Downvote
I imported the Prospect_Applicant table inot the Campus6 DB
here is the sql with the joins. I want to have Recruiter_Selected come back with all the recruiteres that have a -1 in the field designated as a Yes.

SELECT DISTINCT
dbo.PEOPLE.PEOPLE_ID, dbo.PEOPLE.FIRST_NAME, dbo.PEOPLE.LAST_NAME, dbo.ACADEMIC.ACADEMIC_YEAR, dbo.ACADEMIC.ACADEMIC_TERM,
dbo.ADDRESS.ADDRESS_LINE_1, dbo.ADDRESS.STATE, dbo.ADDRESS.CITY, dbo.ADDRESS.ZIP_CODE, dbo.ADDRESS.DAY_PHONE,
dbo.ADDRESS.EVENING_PHONE, dbo.ADDRESS.EMAIL_ADDRESS, dbo.volunteer_interest.people_code_id,
dbo.volunteer_interest.last_name AS Recruiter_LName, dbo.volunteer_interest.first_name AS Recruiter_FName,
dbo.volunteer_interest.address_line_1 AS RecruiterAdd, dbo.volunteer_interest.city AS Recruiter_City, dbo.volunteer_interest.state AS Recruiter_State,
dbo.volunteer_interest.zip_code AS Recruiter_Zip_Code, dbo.volunteer_interest.email_address AS Recruiter_Email,
dbo.Prospect_Applicant.Select_Recruiter
FROM dbo.Prospect_Applicant INNER JOIN
dbo.PEOPLE ON dbo.Prospect_Applicant.Recruiter_People_Code_ID = dbo.PEOPLE.PEOPLE_CODE_ID AND
dbo.Prospect_Applicant.Applicant_People_ID = dbo.PEOPLE.PEOPLE_CODE LEFT OUTER JOIN
dbo.volunteer_interest RIGHT OUTER JOIN
dbo.ADDRESS ON dbo.volunteer_interest.city = dbo.ADDRESS.CITY AND dbo.volunteer_interest.state = dbo.ADDRESS.STATE ON
dbo.PEOPLE.PREFERRED_ADD = dbo.ADDRESS.ADDRESS_TYPE AND
dbo.PEOPLE.PEOPLE_CODE_ID = dbo.ADDRESS.PEOPLE_ORG_CODE_ID LEFT OUTER JOIN
dbo.ACADEMIC ON dbo.PEOPLE.PEOPLE_CODE_ID = dbo.ACADEMIC.PEOPLE_CODE_ID LEFT OUTER JOIN
Institute.dbo.SemesterInfo Sem ON dbo.ACADEMIC.ACADEMIC_YEAR = Sem.academic_year
WHERE (dbo.ACADEMIC.ACADEMIC_FLAG = 'n') AND (dbo.ACADEMIC.APPLICATION_FLAG = 'y') AND (Sem.offset = '0') AND
(dbo.ACADEMIC.ACADEMIC_TERM = 'Fall')
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
226
Mike Lewis
Mike Lewis
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
278
Andrzejek
Andrzejek
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
446
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Adding Text to and XML Field 1
Replies
2
Views
109
grnzbra
grnzbra
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
215
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top