SELECT ... JOIN On Two Databases In VWD (?)

May 26, 2006

My environment:
XP Home, VWD, SQLEXPRESS.
A purely local setting, no network, no remote servers.

I try to do a JOIN query between tables in the membership ASPNETDB.mdf and one table in a self created 3L_Daten.mdf.

After dragging the tables into the Query Design window and connecting them VWD creates this query (here I added the control declaration):

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAspNetDB %>"
            SelectCommand="SELECT aspnet_Users.UserName,
                                 aspnet_Membership.Password,
                                 aspnet_Membership.Email,
                                 aspnet_Membership.PasswordQuestion,
                                 aspnet_Membership.PasswordAnswer,
                                 aspnet_Membership.CreateDate,
                                 aspnet_Membership.LastLoginDate,
                                 aspnet_Roles.RoleName,
                                [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Age,
                                [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Sex,
                                [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Area
                            FROM [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten
                            INNER JOIN
                                aspnet_Users
                            ON
                                [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.User_ID = aspnet_Users.UserId
                            LEFT OUTER JOIN
                                aspnet_Roles
                            INNER JOIN
                                aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId
                            ON
                                aspnet_Users.UserId = aspnet_UsersInRoles.UserId
                            LEFT OUTER JOIN
                                aspnet_Membership
                            ON aspnet_Users.UserId = aspnet_Membership.UserId">
</asp:SqlDataSource>

THIS WORKS, BUT:

As you can see the database 3L_Daten.mdf is inserted with its full path, which is not feasible for deployment reasons.

My question: How can I address both databases purely by their database names ? Both have been created within VWD and lie under App_Data.

(I tried almost everything, I practiced with the SQL Server 2005 Management Studio Express Edition, I tried linked servers, all without success).

Thank you for your consideration.

 

 

View 3 Replies


ADVERTISEMENT

How To Join Tables From Different Databases In SQL Select Statement?

Apr 30, 2008

I have a basic sql statement, where I have a usersID, and I want to joing that usersID to another table in another database to get the users first and last names.  How do I join across databases... each with a different connection string? 
 Here's what I want..
Select usersID from tableA in databaseA, and usersFirstName, usersLastName from table B in database B where the usersID from tableA = the usersID in tableb. 

View 6 Replies View Related

Select Command - Left Join Versus Inner Join

Aug 9, 2013

Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?

View 4 Replies View Related

Join 2 Tables From 2 Different Databases

Feb 6, 2007

Hi All,
 Is it possible to join 2 tables from 2 different databases in Stored Procedure.
Thanks in advance.

View 2 Replies View Related

How Can I Join Two Tables From Two Different Databases?

Aug 3, 2007

hi,
i have one database ASPNETDB.MDF created by default when adding a user to my site, and MyData.mds - my database...i want to join the aspnet_Users table with another table created by me (in myData.mds), how can i do that? is hard if i should re-write all the data from myData into the ASPNETDB,i even writed both connectionStrings in the web.config but still with no succes...
is there any trick in the SQL statment? please help me
thank you

View 10 Replies View Related

Join Table From Different Databases?

Apr 16, 2008

I would like to join table in different databases with a primary key and query it. How to do ?

View 3 Replies View Related

Inner Join On Two Separate Databases

May 30, 2008

I need to do a inner join on tables from two separate databases.  I understand that you can do this by using this type of syntax:
select a.col1, b.col2from db1.dbo.tab1 a, db2.dbo.tab2 bwhere a.col1 = b.col2however, how do I reference the two databases in the following code?
Thanks,
Tim
 
Function GetConnectionString() As StringDim ConnectionString As String = ConfigurationManager.ConnectionStrings("MainWeb").ConnectionString
Return ConnectionString
End Function
 Using conn As New SqlConnection(GetConnectionString())
conn.Open()
Dim sql As String
sql = "SELECT CaskInfo.CaskID, CoCInfo.CoCName, AmendmentInfo.AmendmentName FROM CaskInfo INNER JOIN CoCInfo ON CoCInfo.CoCID = CaskInfo.CoC INNER JOIN AmendmentInfo ON AmendmentInfo.AmendmentID = CaskInfo.Amendment WHERE "For i = 0 To UBound(words)
If i > 0 Then sql = sql + " OR "
sql = sql + "(CoCInfo.CoCName + ' ' + AmendmentInfo.AmendmentName) LIKE '%" + words(i) + "%'"
Next
' lblResults.text = sql' Exit Sub
Dim com As SqlCommand = New SqlCommand(sql, conn)
Dim result As SqlDataReader = com.ExecuteReader()
Dim SearchResults As StringWhile result.Read()
SearchResults = SearchResults + result.GetInt32(0).ToString + " " + result.GetString(1) + " " + result.GetString(2) + "<br>"
End While
result.Close()
lblResults.text = SearchResults
conn.Close()
End Using

View 5 Replies View Related

Join Between Tables On Different Databases

Jan 22, 2004

Is it possible to make a join between two tables from different databases on same server? if yes, how can we do that?

And also if I want to make a join between tables on different databases on different server?how to do this. Please advise.

View 2 Replies View Related

How To Run A JOIN Between Databases On Two SQL Servers

Feb 23, 2008





Code Snippet
SELECT ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice), a.MoeenName_L1
FROM ReciptItems LEFT OUTER JOIN
Acc_mydbname.dbo.Categories AS a ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, a.MoeenName_L1





How Replace Acc_mydbname with (SELECT AccountDBName FROM Config)

(SELECT AccountDBName FROM Config) ='Acc_mydbname_2008.dbo.'

View 6 Replies View Related

Join Tables From Two Databases

Mar 1, 2007

Say I have two database files, database1.sdf and database2.sdf, how can I make a select that joins tables from both?

I'm using C#.

Thanks!

View 1 Replies View Related

How Do I Join Tables From Different Databases (under Same Server Though)

May 12, 2006

Hey, I have two databases (db1 and db2) under the same server. How do I combine tables from both of them?I searched the forum and triedSELECT

View 3 Replies View Related

Join 2 Tables Exist In Two Databases

Nov 23, 2005

Hi,
I want to get the results of a query between 2 tables that exist in the same server but in 2 different databases. I want this query to be executed in a stored procedure (use the reults in a cursor), or if it's possible to be used in an ado dataset or a simple dataset in Delphi. Can this be done also if the two datases (MSSQL) exist in different servers??
Can anyone show an example of how to accomplish these tasks??

Best Regards,
Manolis Perrakis

View 9 Replies View Related

Query And Join Between 2 Databases On Same Server?

Nov 9, 2011

I am trying to query 2 SQL databases on the same server and inner joining together.

Databases: Goldmine_MIOA
Goldmine_GMBase

From the "Goldmine_MIOA" database I need to query contact1.* and from the "Goldmine_GMBase" database i need to query cal.

I would then like to inner join both these together.

View 12 Replies View Related

Join Tables In Same Instance Different Databases

Sep 28, 2015

I have a problem in that a database I have created required splitting into three databases. The records were similar but reporting and maintenance differed enough that splitting the data seemed the best option. So now I have three databases that are needing to be updated from one file and I am not sure how to do that. See the illustration below. The Machine table is a one to many to the Job table, the Job table is a one to one with the Run Data.

The first idea was to query the first database if the record was not found go to the next. If no match was found in any table drop it in a reject bucket. Then what about perhaps a lookup table with starting and ending Job ranges. I would query it to find the correct database then do another query to update the record. But then I could have some type of joining table. Not sure how that would look across databases.

The tables I am updating or adding new records to has the same fields, unique key across all the databases. I could make one big table which gets rid of the query to find the correct database to update but I still would have to connect this back to the Job table which brings me back to a join table or index table.

View 6 Replies View Related

SQL 2012 :: Compare And Join Databases

Feb 7, 2014

A customer has messed up while moving their databases. After working for a week they found that data is missing in the database.I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.

I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database. Join the data in the two databases so to say. Both databases are from the same application so they use the same users, schema and so on.

View 9 Replies View Related

SSIS - How To Run A JOIN Between Databases On Two SQL Servers?

Jan 17, 2006

Hi All,

I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 I would simply do the following:

From Server A:

sp_addlinkedserver B

INSERT dbo.ResultsTable

SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable

INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID

sp_dropserver B



What do I need to do to perform the same operation in SSIS world?

Thank you !

View 1 Replies View Related

Transact SQL :: Select From A Select Using Row Number With Left Join

Aug 20, 2015

The select command below will output one patient’s information in 1 row:

Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2

[code]...

This works great if there is at least one OP coverage.   There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.   The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.  

select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,

code]...

View 6 Replies View Related

DB Design :: Join Two Table From Two Different Databases But Same Server

Nov 9, 2015

I am trying to join two table from two different databases.

Database 1 = Agent and Table = Stats 
Database 2 = Amount and Table = Sales 

The common field is Expr1 (table1) and Initials (table2)

View 7 Replies View Related

Select From 2 Separate Databases

Jul 21, 2000

How do you select data from 2 separate databases residing on 2 servers?

Is there a way?

Angel

View 1 Replies View Related

Select Table From All Databases

Jan 23, 2004

i need to create a rowset containing database names where a particualr table name exists.
ie. in Oracle i would select owner from sys.all_tables where table_name='MYTABLE'

I know master.dbo.sysdatabases contains my owners, and in each database sysobjects would identify MYTABLE ..
sp_tables only lists those in the current database, but i want to list all databases that contain MYTABLE

a single select statement would be nice.

View 14 Replies View Related

How To Select Data From Different Databases

Jan 10, 2008

Hi I'm trying to select data from different databases, Actually databases have the same structure and tables but different data, so I want to mix data from specific tables of all databases.
Any help would be great

View 5 Replies View Related

Select Databases Not Already In A Table

Jun 4, 2007

Hi,

I'm quite new to MS SQL and am having a few problems I hope someone here can help me with.

I've created a small web application that enables users to backup and restore databases easily.

There is one table that holds information about the backups that have been taken and one table that holds details about each database (name, location etc)

What I want to do is have a web interface that allows a user to link a new database (one that is already attached to SQL but) to the system by inputting it into the database details table.

Ideally this will be a drop down menu that shows the databases attached to the server that arnt:
1. Already in the backup system table
2. Not the system databases.

This will allow the user to easily select the database and give it a title (usually different from the local name of the database) for identification.

The database local name, and title would then be input into the database details table.

I'm thinking that I need to use a SELECT statement, but I'm not quite sure how I can restrict the results to databases that arnt already attached or already in the system.

I hope that wasn't too muddled an explanation!

Thanks,

Steve

View 8 Replies View Related

Parameter To Select Among Databases

Feb 25, 2008

I have several databases on the same server that have the same table structures within them. Call them

Server.A
Server.B
Server.C
...etc.

I have designed a report that works in one database, say "A". I would like to add a parameter box to the report giving the user the ability to select which database to run the report in. I'm not sure if this is possible.

There may be more complex ways to handle this, say, with dynamic SQL or by building a large UNION of select statements across the various databases. But I figured I might ask for a simple solution first. :-)

View 3 Replies View Related

DTS: Single SELECT From 2 Databases Possible?

Jul 19, 2006

Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?

I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:

SELECT
a.something
FROM
DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c
WHERE
a.PCat <> c.Pcat

(where DB_ONE and DB_TWO are the 2 different db names)

I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?



Thanks,

Andre

View 1 Replies View Related

Select Data From Two Databases On Different Servers

Oct 27, 1999

Hi there,
I was just wondering is it possible to select data from two different databases on two different servers?

ie Select * from
Server1.databasename..table, Server2.databasename..table


If anyone has any suggestions they would be appreciated,
Thanks,
Fin

View 2 Replies View Related

SQL 2012 :: SELECT / UPDATE On All Databases

Nov 20, 2014

I need assigning a group of users read, write, update (not delete) permission on all user databases. is there any way to do so instead of creating role on each database separately.

View 2 Replies View Related

Select Or Join

Oct 4, 2000

What is the difference from performance point of view, when you select from 3 different tables to show fieldnames across the 3 tables based on one common key, OR using Join between the 3 tables.

Thanks

View 1 Replies View Related

SELECT INNER JOIN SUM

Jun 14, 2008

I have three tables.


Quote
QuoteID, QuoteNumber,


Transportation

TransportationID, QuoteID, Item, Description, Cost

OptionalCharges

OptionalChargesID, QuoteID, Item, Description, Cost



What I want to do is SUM(Cost) for the Transportation and the Optional Charges table. If I do a normal INNER JOIN, with the SUM for Cost on the table Transportation and Optional Charges, it will SUM each twice. For example if Transportation has three rows with a cost of 10, 20, 30, it will SUM a total of 120 instead of 60.

So I came up with:

Select
Quote.QuoteID, QuoteDate, t.TransportationTotalCost

FROM
Quote.Quote
INNER JOIN
(

SELECT
QuoteID, SUM(COST) AS TransportationTotalCost
FROM
Quote.Transportation
GROUP BY
QuoteID
) t
on Quote.QuoteID = t.QuoteID
WHERE Quote.QuoteID = 135

Which gives me the total for the Transportation Table, but how do I go about adding in the Optional Charges Table?

Thanks,

marly

View 2 Replies View Related

SQL Select/Join Help

Jun 22, 2008

Hi,

My SQL is a little rusty, and I need a little help for a client that I'm helping.

I'm simply trying to do create the following output:

HEADINGS: Sale Order | Customer Number | Customer Name | # of Pallets | (etc)
DATA: 456188 | 12355890 | Acme Customer | 4 | other stuff I have figured out that is irrelevant


This data comes from 3 tables:
1) SOE_HEADER
SALE_ORDER_NO | CUSTOMER_NO | (etc)
456188 | 12355890

2) CUST_NAME
CUSTOMER_NO | CUSTOMER_NAME | (etc)
456188 | Acme Customer

3) PALLETS_SALES_ORD
SALE_ORDER_NO | PALLET_ID | (etc)
456188 | 12345
456188 | 67890
456188 | 13579

I have 2 queries that independently pull the right data. 1 query joins the customer info to pull in the customer name, and the 2nd query calculates the # of pallets per sales order.

BUT I CAN'T GET THEM TO WORK WHEN I DO A JOIN!!! Can someone please help me? Here are the queries as I currently have them. This returns zero rows, but when I run my queries independently, the both return multiple rows with the correct data. PLEASE HELP ME!

SELECT
SOE.INSIDE_ROUTE,
SOE.SALESMAN_NO,
SOE.SALE_ORDER_NO,
CUST.CUST_NAME,
SOE.CUSTOMER_NO,
(SOE.SALES_AMT/100) AS SALES,
(SOE.COST_GOODS_SOLD/100) AS COGS,
(SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN,
COUNT(PALLET.SALE_ORDER_NO) AS NumOccurrences
FROM
SOE_HEADER SOE,
CUST_NAME CUST,
PALLET_SALES_ORD PALLET
WHERE
SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO
AND SOE.CUSTOMER_NO = CUST.CUSTOMER_NO
GROUP BY
PALLET_SALES_ORD.SALE_ORDER_NO
HAVING (COUNT(PALLET_SALES_ORD.SALE_ORDER_NO) > 1 )

THANKS. This will be a big help for the work I need to get done tomorrow, and I can't take it anymore. I've tweaked this all weekend, to no avail.

View 17 Replies View Related

Select Min Value In An Inner Join

Nov 28, 2006

Hello,

I'd appreciate any help with the following problem.

I'm trying to update a table using an inner self join.

I've a list of historical records with start dates, and I need to add end dates to the records, using the start date of the next record.

The table I'm using looks like this
CREATE TABLE [dbo].[IbesEstimateHist](
[IbesEstimateHistId] [int] IDENTITY(1,1) NOT NULL,
[IbesEstimateId] [int] NULL,
[EstimateDate] [datetime] NULL,
[EstimateEndDate] [datetime] NULL CONSTRAINT [DF_IbesEstimateHist_EstimateEndDate] DEFAULT ('9999-12-31 00:00.000'),
[Value] [decimal](13, 4) NULL,
CONSTRAINT [PK_IbesEstimateHist] PRIMARY KEY CLUSTERED
(
[IbesEstimateHistId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

and here's some example data

insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value])
values(1,'2006-01-01','9999-12-31',100)
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value] )
values (1,'2006-02-01','9999-12-31',100)
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value])
values (1,'2006-03-01','9999-12-31',100)

These are three historical records for the same estimate, I want to set the end dates of the earlier records to the start date of the next record that was recieved.

This is the SQL that I've tried using but I can't seem to get it right

select esth1.IbesEstimateId, esth1.EstimateEndDate,min(next.estimatedate)
from IbesEstimateHist esth1
inner join
(
select esth2.EstimateDate as estimatedate, esth2.IbesEstimateId
from IbesEstimateHist esth2
) as next
on esth1.IbesEstimateId = next.IbesEstimateId
and esth1.EstimateDate < next.estimatedate
group by esth1.IbesEstimateId, esth1.EstimateEndDate

I'd be grateful for any help, thanks.




















Sean_B

View 3 Replies View Related

Select With Join

Jan 29, 2007

hi all,

i have a doubt regarding a select operation performed on two tables with join statement.

The table structure is as follows


table name- application
---------------------- ------------------------------------------
appid appname version
------------------------------------------------------------------
1 Test 10
2 Test 11
3 Sample 5

table name- app_users
-----------------------

app_users_id user_id appid version date_downloaded
------------------------------------------------------------
1 250 1 10 1/29/2007




Now i want a grid which should show data like this

AppName LastDownloadedVersion Date_Downloaded
----------------------------------------------------------
TestV11 10 1/30/2007
SampleV5 -- ------


Here the value for appname should be created by appname+'V'+latest version of that appname(eg. Application name- Test Ltest version- 11 thus appname becomes 'TestV11')

Could anybody help me to solve this problem..
Thanx in advance..

View 1 Replies View Related

How To Select With Inner Join

Aug 5, 2007

Hi:

I have a record that has location1, price1, location2, price2

How would I do an inner join or "how would I " get the name of the location?

location table -- locationid, locationname
producttable -- location1, location2 is the locationid in location table

View 1 Replies View Related

Price Of Using Multiple Databases In SELECT Statements

Sep 11, 2007

Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?  

View 2 Replies View Related







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