Solved

Need help finishing view/query for SQL backend

Posted on 2007-04-10
2
163 Views
Last Modified: 2010-03-20
I am working on a sales database that is needs to be linked into a seperate database on the same server to pull some information. Both are SQL.  I have partially created the query i need in a view on the sales db side. But what i need to do now is finish the query so it populates the sales side with the needed info.  Or atleast gives me the view to where i can display it (the frontend is access 2003). I have a blank field that has contractID on the sales side.  I have a populated field with the contractID in the other database. I also have OPPID that acts as the key on both sides.  Can someone help me finish this query

SELECT     TimeMatters6.tm6user.matter.mat1_04_09, dbo.Job.OpportunityID, TimeMatters6.tm6user.matter.mat_no
FROM         TimeMatters6.tm6user.matter INNER JOIN
                      dbo.Job ON TimeMatters6.tm6user.matter.mat1_04_09 = dbo.Job.OpportunityID

I need to take the results and be able to view them in the sales database. I'm very new to sql so please be as literal as possible!!
0
Comment
Question by:riuser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18884455
Can you provide a list of all of the fields from each table that you want in the view?
Or, do you want one update statement to update ContractID? Also not sure about the fields your using to join - in your query you are joining matter to OpportunityID, but then you said OPPID acts as the key on both sides.

When using long table names it helps to use a short alias.  Here is an example of a cross-db view:

CREATE View YourViewName
AS

SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM Database1.User1.Table1 a
INNER JOIN Database2.User2.Table2 b ON a.KeyID = b.KeyID

Here is how an update query would look:
UPDATE a
SET a.Field1 = b.Field1
FROM Database1.User1.Table1 a
INNER JOIN Database2.User2.Table2 b ON a.KeyID = b.KeyID
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18950635
Did that help?
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question