I Want To Join Two Colums From Two Differet Database

May 1, 2008



Hello I have a Source database and a Target database.

I want to join one table from the source to the other table in the target.

Please can some one write a sql query for this.

i gues its something like

select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination

Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.

View 6 Replies


ADVERTISEMENT

INNER JOIN: Joining Two Different Colums With One Table?

Nov 15, 2004

Hello everyone,

I'm stuck on something here. Any help would be great. This is a relational database question.

I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.



Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_SubCategories.SubCategoryName,
_SubCategories.SubCategoryName,
LinkName
FROM
_Links
INNER JOIN
_SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId
INNER JOIN
_SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable



Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:



Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_Links_Categories.CategoryName,
_Links_StatusCategories.StatusName,
LinkName
FROM
_Links
INNER JOIN
_Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId
INNER JOIN
_Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable


I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.

Table_Categories: CategoryId (Primary Key), CategoryName

Table_SubCategories: CategoryId, SubCategoryId (Primary Key), SubCategoryName

So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.

If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.


Alec

View 3 Replies View Related

How To Copy A Column(or Colums) From A Table In One Database To Another Table In A Different Databas

Oct 1, 2001

How do I copy a column(or colums) from a table in one database to another table in a different database

View 1 Replies View Related

Rows As Colums

May 8, 2007

I know this is possible in DB2 and Oracle, but what about for SQL-server 2005
   
1)      select X number of rows from table1 
2)      I need  colums for each row of table1 in a new table
3)      As such,  Select   (select * from X where x.id = @ID), a,b,c    from table Y where y.Id = @ID
And I dont want to use IfExists.
Thanks
DK
 

View 1 Replies View Related

Query Across All Colums

May 18, 2004

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?

Thanks

Shabassa

View 12 Replies View Related

Updating Colums

Nov 20, 2006

Hello all.

I have a column named 'RELATED' of type text in a table. I want to update the contents of this colums...........but still keep what is currently stored in it. Basically i want to add some text onto the end of the string it currently contains.

Any help would be much appreciated.

View 1 Replies View Related

Pivoting Row Values Into Colums

Oct 11, 2004

I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:

Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91

I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%

This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.

But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.

I can restructure the database, if needed.

I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.

It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?

View 1 Replies View Related

Convert Rows To Colums

Aug 26, 2004

I am getting this from my client
TableA
StorID LineNumber Text
30000 1 ClientName
30000 2 ClientSurname
30000 3 3333333
30000 4 20-05-2004

I actually want this to look like
TableB
StoryID Name Surname Policy Date
30000 Name Surname 3333333 20-05-2004

Can you help with the script to convert this to one record with many fields (as in TableB) if this is

View 4 Replies View Related

Bcp Command To Give Colums

Jan 8, 2004

Can someonte tell me that if i
bcp bda..mytable out c:discounts.xls -c -p , how can I put the first row as my column names since I get only data

View 2 Replies View Related

Repated Colums Returned

Jul 23, 2007

Hello All, first time post here and really stuck on this issue.

I have 4 Tables that i am Joining Across. Each one of the tables has around 800,000 Rows in it. I am Joining them all on a field Called MDRReport Key.

my query looks like this

SELECT Mdrfoit.MDRReportKey, Mdrfoit.DtReport, Mdrfoit.DateReceived, Mdrfoit.EventType, Mdrfoit.AdverseEvent, Mdrfoit.ProductProb, Mdrfoit.ReportOccup,DevTable.BrandName, DevTable.ManName, DevTable.ModelNumber, DevTable.ProductCode, DevTable.DeviceFamily, TextTable.Text

FROM Mdrfoit
INNER JOIN DevTable ON Mdrfoit.MDRReportKey = DevTable.MDRReportKey
INNER JOIN TextTable ON Mdrfoit.MDRReportKey = TextTable.MDRReportKey LEFT OUTER JOIN probLink ON Mdrfoit.MDRReportKey probLink.MDRReportKey

WHERE Mdrfoit.DateReceived BETWEEN '1/1/1995' AND '1/1/2007') AND
AND (DevTable.BrandName LIKE '&PLV%')

My problom is that when i do this i get back 3 rows in my result set for every 1 result that i want So ill get like

2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK


So my result set is on average 3 time larger then its suppose to be, This is also causing my query time to be very very slow.

There are no Duplicate entries in the database where all of the fields are the same. I am confused on if this is a problom in my query. Any advice would be great






"When you have everything to loose and nothing to gain, All-In is not an option"

View 1 Replies View Related

Validating And Updating Colums

Jul 23, 2005

Hi all,I am a newbie to sql and I need your help.I want to update column (email) from one table to another validating theCustomerid column in both table. Update the email address in productiontable with the email address in temp table if the customerid is same in bothtables.What would be the query?Thanks,

View 3 Replies View Related

Matrix Subtotal But Not All Colums

Feb 21, 2007

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.



Thanks to all!

Andrea

View 4 Replies View Related

Joined View Does Not Contain All Colums

Jul 20, 2006

The joined view is named "dbo.viewExecView" and is like:

SELECT Bank_No, data_center
FROM [ALPHASQL2000].ev_db.dbo.Bank

The new view that joins to the above view is like:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.

I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.

Regards,

Joe

View 4 Replies View Related

Variable Colums In Stacked Table

Dec 14, 2007

Hello, and thanks for taking the time to read this.

NOOB question:

In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:

CREATE TABLE [dbo].[T_Catalog](
[StlyeID] [int]
) ON [PRIMARY]
with data:
1
2


the SIZES table (filled in by the customer with all the size ranges they carry) is:

CREATE TABLE [dbo].[T_Sizes](
[SizeID] [int],
[SizeName] [nchar](10)
) ON [PRIMARY]
with data:

1,Small
2,Medium
3,Large
4,Xtra-Large

and the AVAILSIZES table would be:

CREATE TABLE [dbo].[T_AvailSizes](
[StyleID] [int],
[SizeID] [int]
) ON [PRIMARY]

1,1
1,2
1,3
2,1
2,3
2,4

Basically, then, we know that:

style 1 comes in Small, Medium, and Large
style 2 xomes in Small, Large, and Xtra large

WE know that, but getting SQL to tell us that is a major PIA!!

Now,

SELECT
t_Catalog.StyleId, t_AvailSizes.SizeID
FROM
t_Catalog
INNER JOIN t_AvailSizes ON
t_Catalog.StyleId = t_AvailSizes.StyleId

will give me a nice list of each item number with a separate row for each size number.

My questions are:

How do I get the size NAMES?

How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?

Is this the right way of doing this?

In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.

Thanks for any help and information you can provide.

View 2 Replies View Related

Order By Colums Asc Does Not Work In SQL SERVER 2005

Feb 12, 2008

Hello
 
Iam usng sql server 2005 database
 
I had this table
 
CREATE TABLE [dbo].[Companies](
[CompanyID] [smallint] IDENTITY(1,1) NOT NULL,
[Company] [nvarchar](40) NOT NULL,
 
I entered Arabic Names
 
so I write this query
select * from companies order by company asc
--and this one
select * from companies order by company
 
and I try to order desc
 
but the row didnt appear out ordered
 
and the big prtoblem happened when I try to update a company Its go to the last row when it appears
 
please help me
 
thanks

View 9 Replies View Related

How To Dynamic Specify The Colums In Full-Text Search?

May 14, 2004

My problem is simple: i want to dynamic specified the columns in the ContainsTable, this is possible? Please see the example.


Declare @Test1 int
Declare @Test2 int
Declare @Query varchar(50)
Declare @Temp varcahr(50)

--Test
Set @Test1=1
Set @Test1=0
Set @Query='something'

--Add the column to put in containstable
IF (@Test1=1)
Begin
Set @Temp='ID'
End

IF (@Test2=1)
Begin
Set @Temp= @Temp + ',Name'
End

SELECT *
FROM
<table>
INNER JOIN
CONTAINSTABLE (<table>, @Temp, @Query) AS KEY_TBL
ON <table>.ID = KEY_TBL.[KEY]



Thanks

View 1 Replies View Related

Showing Colums And Rounding 2 Decmial Places

May 8, 2007

Okay I have a problem and I have no idea to do it. I need the SQL syntax to come up with this:
the formatting is messed up though


a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.

DNAMELOCNumber of PeopleSalary
-------------------------------------------------------------------
ACCOUNTINGNEW YORK3 2916.67
RESEARCHDALLAS52175
SALESCHICAGO61566.67

View 2 Replies View Related

Auto Fill In Colums Trough Foreign Key Relationship

Jun 10, 2004

Hi,

I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,

Can someone help me with this?

Cheers Wim

View 2 Replies View Related

Is There A Way We Can Implement Business Logic By Storing It In Colums Of A Table And Calling It?

Jul 20, 2005

What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg

View 1 Replies View Related

Multiple-step OLE DB Operation Generated Errors. Adding Colums Using ADOX

Nov 26, 2007

Hi
I'm trying to add columns to an existing table using ADOX. I'm using C++/CLI. My code is as follow

Table->default->Append("new_column", ADOX:: DataTypeEnum::adWChar, 255);
Table->default["prev_instance_id"]->Properties["Jet OLEDB:Compressed UNICODE Strings"]->default = true;

The first line of code works fine but when I try to set the value of the property I get the following excpetion:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll
Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Any idea?

Marco

View 7 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses


Thank You

View 1 Replies View Related

Cannot See The Colums In The Design View Of Queries SQL 2000 And MSAccess 2000(adp)

Nov 21, 2006

Cannot see the Colums in the "design view" of Queries. All i see when i want to design a new query is *columns

This happens in only one database, in other databases using same server i can see the colums and can tick them to view then in the query.

In enterprise manager i see all the columns.

Using SQL 2000 and MSAccess 2000

View 1 Replies View Related

Join Database

May 9, 2001

Good Mornig my fryends, my english is more or less but....
I need help, I need commit transacions between TWO databases maintenance referencial integrity.

example:(sql server 2000)

DATABASE 1 <------------------ODBC/BDE(Delphi 5.0)--------DATABASE 2
TABLE 1 TABLE 2
*colunm a <--Query(ODBC(SELECT(*),UPDATE(set),DELETE)----*colunm b
colunm a1 colunm b1

Enforce relationship for INSERT and UPDATES
Cascade Updated related Fields
Cascade Delete related Fields

Why insert the data containg in DATABASE 1.TABLE 1.COLUNM A in
DATABASE 2.TABLE 2.COLUNM B.

Whait for solucions.
" good bless You "

View 1 Replies View Related

Multi Database Join

Apr 8, 1999

Is it possible to create a join between two SQL Server DB's on the same server? What is the syntax?

View 1 Replies View Related

How To Join 2 Table Both Having Different Database?

Mar 26, 2008

View 4 Replies View Related

Join Two Table From Different Database. URGENT!!

Feb 9, 2006

Hi,
anyone could help me on how to do the inner join of two table from two different database. Below is my scripts:
Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))
Dim sqlCommand2 As New SqlCommand("", SqlConnection1)
Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))
Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)
Sub Page_Load(Source as Object, E as EventArgs)
sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"
 sqlconnection1.Open()
............................
The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me.
Ann123 
 

View 4 Replies View Related

Inner Join Using A Query On A The Same Remote Database

Jan 20, 2000

I have an sql 7.0 server. I need to run a query on a remote oracle server then take that output and inner join it with another table on the remote oracle server... then I need to put it a table in a database on my sql server... Anyone know how to do it? Help would be greatly appreciated...

View 2 Replies View Related

Is It Possible To Make Use Of Database Relationships In A Join?

Aug 2, 2004

When you make a join, it may look something like this:

SELECT Products.ProductName from Products, Categories
WHERE Categories.CategoryName = 'Seafood' AND
Products.CategoryID = Categories.CategoryID

In this query you use 'Products.CategoryID = Categories.CategoryID' to give the relation between the two tables. But if you already added the relationships to your database, this should be known.

My question is, why is it necessary to add the relation in the query when the relationships are already in the database? Is it somehow possible to ommit the relation in the query and make use of the relationships in the database.

Kind regards,
Iwan Rotteveel

View 5 Replies View Related

Database Diagram Set Cardinality On Join

Sep 14, 2006

sql server 2005
sql server mangement studio

Hi I am using the databse diagrammer to create a database.
creating the tables and fields is no problem but there seems to be a lack of choice with joins and I cannot see how to set cardinality.
any help appreciated.

B

View 1 Replies View Related

Database Name As Variable In A Join Query In SQL

Feb 2, 2007

I have a very simple Join Query like
Select A, B , C
From Database1.dbo.Table1 Inner Join Database2.dbo.Table2

Now problem is that Database name for the project freuqently changes though Table1 and Table2 names remain constant.
How can I store Datbase name at a central place in a table or as a constant so that I can use them in above query. It will allow me to change them at one place without making changes in all the sps where they are used

View 1 Replies View Related

Join With Table On Another Database Not Working

Dec 9, 2007

Hi,I have two databases on the same server: COMU0002 and 2007DUand two tables: tecnicos on COMU0002 and telf_cli ON 2007DUBeing on COMU0002 this query doesn't work:SELECT * FROM tecnicosLEFT JOIN 2007DU.dbo.telf_cliON ...The error says: INCORRECT SYNTAX NEAR '2007'Even this query being on 2007DU fails on management studio:SELECT * FROM 2007DU.dbo.telf_clibut SELECT * FROM telf_cli works okAny ideas?Thank you!

View 2 Replies View Related

Selecting Distinct Top 3 Rows From Database Using Join

Jun 25, 2007

Hi guys,

Just trying to select a set of Articles from a SQL Server Database. The Articles all have a Category ID which is stored in another table (as an Article could be in more than one Category). I want to select the Top 3 Articles in a Category. At the moment I have as my SQL;

"SELECT TOP 3 f.ArticleID, f.Heading, f.Summary, f.WrittenDate, f.ArticleURL FROM feedTable f LEFT JOIN Categories c ON f.ArticleID = c.ArticleID WHERE c.CategoryID=" + CategoryID + " AND c.ArticleID<>" + id + " ORDER BY c.CategoryID"

Which seems to work to an extent in that I do get three articles in the same Category appearing. However, there are sometimes duplicates appearing, so I need to incorporate a DISTINCT clause to the above. I'm not sure where to put this in though. Any ideas?

Thanks.

View 6 Replies View Related

Max With Left Join - Extract From Database Of Association

Apr 26, 2013

I need to extract from a database of an association, the name of the associate and its last paid share, but after a day trying I simply cannot extract that data.

The table senhas represents the shares, the table associados the associate data and senhas_associados, represents the shares paid by an associate, as it was a many to many relationship.

To query this, I'm trying to query the share with the maximum id (senha) which is in seassoc_senha_id in senhas_associados which represents a paid share.

Here's what I've tried so far:

Firstly, I've tried this statement, the output is the required, but it doesn't show associates who haven't paid any share which is needed (no records in senhas_associados):

SELECT assoc_nome, senha_desig
FROM associados, senhas, senhas_associados
WHERE assoc_id = seassoc_assoc_id
AND seassoc_senha_id = senha_id
AND seassoc_senha_id IN (SELECT max(seassoc_senha_id)
FROM senhas, senhas_associados
WHERE seassoc_senha_id = senha_id
AND seassoc_assoc_id = assoc_id)

the output:
assoc_nome senha_desig
Carlos CostaMaio
Rodrigo Abril

I've even tried adding this:
SELECT assoc_nome, senha_desig
FROM associados, senhas, senhas_associados
WHERE assoc_id = seassoc_assoc_id
AND seassoc_senha_id = senha_id
AND seassoc_senha_id IN (SELECT max(seassoc_senha_id)
FROM senhas, senhas_associados
WHERE seassoc_senha_id = senha_id
AND seassoc_assoc_id = assoc_id) OR senha_desig is null

To no avail, the output was the same.

Then, I've changed the "tactic" and went to left join:
SELECT assoc_nome, senha_desig
FROM associados
LEFT JOIN senhas_associados ON associados.assoc_id = senhas_associados.seassoc_assoc_id
LEFT JOIN senhas ON senhas.senha_id = senhas_associados.seassoc_senha_id

The output:
assoc_nome senha_desig
Carlos CostaAbril
Carlos CostaJaneiro
Carlos CostaFevereiro
Carlos CostaMaio
Rodrigo Janeiro
Rodrigo Fevereiro
Rodrigo Abril
Pedro SoaresNULL

Now it shows NULL's, but I can't find where to put the max function.

I've attached the relational model of the involved tables.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved