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.
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?
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
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
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.'
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??
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.
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.
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?
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,
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
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
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 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. :-)
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?
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.
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.
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.
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?
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
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.
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
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..
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?
