Join With Table On Another Database Not Working
Dec 9, 2007
Hi,
I have two databases on the same server: COMU0002 and 2007DU
and two tables: tecnicos on COMU0002 and telf_cli ON 2007DU
Being on COMU0002 this query doesn't work:
SELECT * FROM tecnicos
LEFT JOIN 2007DU.dbo.telf_cli
ON ...
The error says: INCORRECT SYNTAX NEAR '2007'
Even this query being on 2007DU fails on management studio:
SELECT * FROM 2007DU.dbo.telf_cli
but SELECT * FROM telf_cli works ok
Any ideas?
Thank you!
View 2 Replies
ADVERTISEMENT
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 4 Replies
View Related
Feb 10, 2007
Hello,
XPSP2
VB 2005 Express
Jet/Access database
One of the Forms of these application display a Jet database table. Why querries Min, Max, Avg do not work?
Thanks,
View 8 Replies
View Related
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
Jan 25, 2011
I have a XML data passed on to the stored proc in the following format, and within the stored proc I am accessing the data of xml using the nodes() method
Here is an example of what i am doing
DECLARE @Participants XML
SET @Participants = '<ArrayOfEmployees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Employees EmpID="1" EmpName="abcd" />
<Employees EmpID="2" EmpName="efgh" />
</ArrayOfEmployees >'
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
the above query produces the following result
EmployeeID EmployeeName
--------------- -----------
1 abcd
2 efgh
How do I join the data coming out from the above query with another table in the database, for example Employee in this case
i.e. somewhat like this (note the following query does not perform the join correctly)
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN
Employee EMP ON EmployeeID = EMP .EmployeeID
My desired output after the join would be
EmployeeID EmployeeName Email Home Address
--------------- ----------- --------------- -----------
1 abcd abcd@abcd.com New York
2 efgh efgh@efgh.com Austin
View 8 Replies
View Related
Jan 7, 2015
I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.
sys.dm_sql_referencing_entities doesn't work here.
Note: i dont want to identify by FK References, for 2 reasons:
1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined
View 1 Replies
View Related
Jul 23, 2005
All,I have a perplexing problem that I hope someone can help me with.I have the following table struct:Permission-----------------PermissionIdPermissionDescriptionUserPermission-----------------PermissionIdUserIdActiveI am attempting to retrieve all records from the permission tablewhether there is a match on UserPermission.PermissionId or not.Therefore I implemented this query, which does not produce the resultsthat I expect:SELECT p.Permission,up.ActiveFROM Permission pLEFT OUTER JOIN UserPermission upONp.[Id] = up.PermissionIdWHERE up.UserId = 3However, if I exec this query, it works as it is supposed to:SELECTp.Permission,up.ActiveFROM Permission p, UserPermission upWHEREp.[Id] *= up.PermissionId AND up.UserId = 3In the first query, only the records that match on "permissionId" arereturned, in the second all records are returned from the Left tableand those records that do not have matching columns are set to null, asit should be. My question is, what have I done wrong here?I am running MS-SQLServer 2000
View 4 Replies
View Related
Jul 7, 2006
I have this queryselectcreated_date,tyg_aging_due_dates.object_type,tyg_aging_due_dates.due_datefrom#tyg_aging_service_metricsright jointyg_aging_due_dateson tyg_aging_due_dates.due_date =#tyg_aging_service_metrics.due_dateand tyg_aging_due_dates.object_type =#tyg_aging_service_metrics.object_typeorder by PS, tyg_aging_due_dates.due_dateBasically the table tyg_aging_due_dates have this for dataobject_typedue_date-------------------report1/1/2005report1/1/2006report1/1/2007image2/1/2006image2/4/2006The temporary table retuns something similarcreated_dateobject_typedue_date-------------------------------6/1/2006report1/1/20056/10/2006image2/4/2006So basically I want to join the two tables and for the due date's thatare missing from my temporary table, I want to display NULL (thus theright join).So my query would returncreated_dateobject_typedue_date-------------------------------6/1/2006report1/1/2005NULLreport1/1/2006NULLreport1/1/2007NULLimage2/1/20066/10/2006image2/4/2006The date fields are of smalldatetime. Object type is a varchar(15).When I run my query though, I get only 2 rows back. No matter if Iswitch the right join to a left, full, inner, whatever. I still onlyget 2 rows back. Is this a known issue?I am running Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft CorporationStandard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
View 4 Replies
View Related
Oct 3, 2006
I have two tables. Days (1-31) and dates (random dates)
If I have a query that is
Select Day, Date
From days LEFT JOIN dates ON days.Day = DAY(dates.date)
Order By Day, Date
The left join will not return all the days in days just the ones that join with dates. It returns as if I am doing and 'Inner join'. What do I need to do different?
Thanks.
View 6 Replies
View Related
Mar 31, 2008
Hi
Please some one help me with this
I have a storedproc
ALTER PROCEDURE [dbo].[usp_updateCustomerList]
(@CustomerID int,
@FirstName varchar(20),
@LastName varchar(20),
@Address1 varchar(35),
@Address2 varchar(35),
@Address3 varchar(35),
@Address4 varchar(35),
@OrderAmount money,
@OrderStatus varchar(6))
as
UPDATE OrderDetails SET Customer.FirstName=@FirstName,Customer.LastName=@LastName,Customer.Address1=@Address1,Custome.Address2=@Address2,Customer.Address3=@Address3,Customer.Address4=@Address4,
OrderDetails.OrderAmount=@OrderAmount,OrderDetails.Status=@Status
From OrderDetails
INNER JOIN Customer
ON OrderDetails.CustomerNumber = Customer.CustomerNumber
where OrderDetails.CustomerID = @CustomerID
When iam executing the stored procedure iam getting error
The multi-part identifier "Customer.FirstName" could not be bound.
Please someone help me with this.
I want to update two tables so ia joining the two tables with customer number which in both the tables.
and customerID is only in OrderDetails table.
so someone please help me with this.
thanks
Ramya
View 6 Replies
View Related
Jan 1, 2015
I found something very strange...stufff function working with self join but not working with left or right join,. I have a table
**Id name**
1 samar
1 Harry
2 jack
I want the output as
**Id name**
1 samar Harry
2 jack
The below query works fine with self join
Select b.id, stuff ((select ` ` + a.name from #test a where a.id = b.id
for xml path (``)),1,1,``)
From #test b
Group by id
But when i do right join i get error _ invalid object name `b`. ....
Select b.id, stuff ((select ` ` + a.name from #test a
right join b on b.id = a.id for xml path (``),1,1,``)
From #test b
Group by id
View 4 Replies
View Related
Sep 11, 2007
Hi,
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30
After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
View 5 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Apr 14, 2015
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
View 2 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Apr 2, 2008
I set up a new mirror server. Everything is good except that the Database Mirroring Monitor is not working for one of the databases. In the monitor the principal data is showing up as blank
If I look at dbm_monitor_data on the principal most of the data columns (e.g. Send_queue_size) are null where as they have data for the other databases.
Both servers are SQL Server 9.0.2047 Enterprise Edition.
Any idea what might be going on here?
Thanks in advance.
View 3 Replies
View Related
Apr 29, 2014
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
View 7 Replies
View Related
Aug 15, 2015
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN
[code] ....
View 9 Replies
View Related
Jul 23, 2005
If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
View 3 Replies
View Related
Mar 10, 2008
Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
View 2 Replies
View Related
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
Nov 5, 2004
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
Thx
View 12 Replies
View Related
Feb 24, 2006
I have one main Table "MainTable" which I want to relate with "subTable1, subTable2, ..." in such a way that:
"ith subTable" have to be related/joind on "ith row" of the "MainTable", "jth subTable" have to be related/joined on "jth row" of the "MainTable" and so on...
What I want Actually?
I want that when ever I delete a Record in the "MainTable", The corresponding "subTable" have to be deleted Itself.
I thought a solution that, I can cerate a Trigger on Delete of the "MainTable" and it would delete the corresponding "subTable". But I dont know how to ceate that.
Secound solution what I thought is, that may be there is some majic power in the Table Joinings. That I might join "MainTable" row with "subTable" ( ofcourse that I dont know either :))
So my question is, that what is the actual solution for my problem?
What ever solution is please give me a sample also with that. Like in a Trigger how can I write some Expression which can delete the "subTable" for the Currunt delete Row.
Regards,
Omer Kamal
View 3 Replies
View Related
Oct 12, 2007
In my Database, there is a Table name Quote which has a column name Quotation. Quotation has some Quote inserted into it.In my web form, there is a Search button with a text box named SearchTextBox. When I enter a word on that text box and click on the Search Button, my resulted page gonna be only those rows from Quote Table which match that word. How can I do it?Hints: Select * from Quote where Quotation like '?????'
View 2 Replies
View Related
May 1, 2008
I am getting a failed error message with this code and i don't understand why.
DECLARE @YourTAble Table (AccountID INT)
Insert INTO @YourTable
SELECT 8003 UNION ALL
SELECT 8015 UNION ALL
SELECT 5033 UNION ALL
SELECT 5033 UNION ALL
SELECT 2115 UNION ALL
SELECT 2678
SELECT AccountID, Ranking =(SELECT COUNT(*) FROM @yourtable as YT WHERE YT.AccountID > @yourtable.AccountID)+ 1
FROM @YourTable
ORDER BY (SELECT COUNT(*) FROM @YourTable YT WHERE YT.AccountID > @YourTable.AccountID) + 1
Dallr
View 2 Replies
View Related
Jan 17, 2006
In query analyzer the correct data is returned.
But when I run it in my application no records ae displayed.
In Sql profiler it says completed and I use the values in query analzyer it works fine. here is my stored proc:
CREATE PROCEDURE [dbo].oc_OnlineCaseOrder
@CategoryId int,
@Order varchar(25)
As
SET @CategoryId = @CategoryId
SET @Order = @Order
If @Order = 'Alpha'
Begin
SELECT
oc.[CaseId],
oc.[StatusId],
oc.[CategoryId],
oc.[Title],
oc.[CaseText],
oc.CourseId,
occ.Description AS CategoryDescription,
ocs.Description AS StatusDescription
FROM
[dbo].oc_OnlineCase oc WITH (nolock)
JOIN dbo.oc_OnlineCaseCategory occ WITH (nolock) ON oc.CategoryId = occ.CategoryId
JOIN dbo.oc_OnlineCaseStatus ocs WITH (nolock) ON oc.StatusId = ocs.StatusId
WHERE oc.CategoryId = ISNULL( @CategoryId, oc.CategoryId )
AND oc.StatusId = 100
Order by oc.[Title]
END
ELSE
Begin
DECLARE @TempCaseIds Table
(
CaseId int,
CategoryDescription varchar(30)
)
SELECT MIN(oca.AuditDate) as "Recent Case Publication", oca.CaseId, occ.[Description]
INTO #TempCaseIds
From
[dbo].oc_OnlineCaseAudit oca WITH (nolock)
JOIN dbo.oc_OnlineCase oc WITH (nolock) ON oc.CaseId = oca.CaseId
JOIN dbo.oc_OnlineCaseCategory occ WITH (nolock) ON oc.CategoryId = occ.CategoryId
JOIN dbo.oc_OnlineCaseStatus ocs WITH (nolock) ON oc.StatusId = ocs.StatusId
WHERE oc.CategoryId = ISNULL( @CategoryId, oc.CategoryId )
AND oc.StatusId = 100
Group by oca.CaseId,occ.[Description]
Select DISTINCT
CaseId, [Description]
From #TempCaseIds
End
GO
View 1 Replies
View Related
May 2, 2008
My Alter Table below doesn't seem to be adding the column, any ideas why? I reference the column later in my TSQL and get a invalid column name error, and sure enough, when I check the table the column is missing.
-Thanks
*******************************************************
CREATE PROCEDURE [dbo].[sp_alk_populate_cust_to_pledge_tbl] AS
-- This procedure should go through each customer and populate the TEMP Table MI_Flat_Customer_Pledge by using Table mi_pledgehold.
-- Table mi_pledgehold is a replicat of the needed info from pledgehold with the addition of an actual primary key
-- Table MI_Flat_Customer_Pledge changes the one to many relationship between Customers and Pledges into a flat table for reporting
-- Table MI_Flat_Customer_Pledge will only show 10 pledges
DROP TABLE dbo.mi_pledgehold
SELECT [cust-num], [loc-id], [pledge-amt], [ticket] into [dbo].[mi_pledgehold] from pledgehold
ALTER TABLE [mi_pledgehold]
ADD [mi_pledge_id] smallint IDENTITY(1,1)PRIMARY KEY
-- Drop and Create the MI_Flat_Customer_Pledge
DROP TABLE mi_flat_customer_pledge
CREATE TABLE dbo.mi_flat_customer_pledge
(
[cust-num] int,
[pledge-amt0] numeric,
[pledge-amt1] numeric,
[pledge-amt2] numeric,
[pledge-amt3] numeric,
[pledge-amt4] numeric,
[pledge-amt5] numeric,
[pledge-amt6] numeric,
[pledge-amt7] numeric,
[pledge-amt8] numeric,
[pledge-amt9] numeric,
)
*****************************************************8
View 7 Replies
View Related
Jun 26, 2007
Hi
I have set up database mail.
I know the settings are correct because I use the same ones for MS Outlook on the same machine.
I am getting the following error.
-----------------------------------
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 6 (2007-06-26T06:25:01). Exception Message: Could not connect to mail server. (No such host is known). )
------------------------------------
Service broker is enabled.
Database mail is started.
i really don't know what else it could be.
...Does SQL Server Agent have anything to do with Database mail?
Any other suggestions gratefully accepted..
ICW
View 8 Replies
View Related
Sep 28, 2007
I have contracted a webproject to be done. My client allready has a website. I need to create another website that replace the existing one with the new look.
This is the problem. The old website has a Database that is associated with user registration & all other data of the company. So, I need to implement the same database to the new website which I am going to develop.
In this case, how can I start? can I add a SQL express databse to the new project and import the data while the development phase? or should I use a SQL server installed while developing the site?
Please give me an easy to understanable reply that I can deal with the old database?
View 6 Replies
View Related
Dec 12, 2005
I need to write a query that requires respective fields referencing from multiple tables.
For example, here are the tables:
Main Table:
InfoID Team1 Player1 Team1
Table: Player_ref
Player Team_Player_ref Player1 John doh
Table: Team_ref
Team Team_Player_ref Team1 My Team
Ideal result Table from query:
InfoID Count John Doh 1 My Team 2
Any suggestion to creat the Ideal Results table from query?
Normally, I could do it if it only referenced from 1 table, I would do an inner join, however, since there are 2 referenece table, doing inner join wouldn't work. A proposed suggestion would certainly be nice. Thanks in advance.
--daydreamstuck at the current problem
View 6 Replies
View Related
Mar 31, 2004
I need to drop a table if exist ?? how can i do that ??
thanx !
View 3 Replies
View Related
Oct 16, 2007
Hi,
We wrote some stored procedures, and some temporary table used because there some some complicated logic.
In normal testing, the application works weel, but very easy to get deadlock error in stress testing.
Error message like this:
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Sample code:
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId int
)
INSERT INTO #PageIndexForUsers (UserId)
SELECT USER_ID
FROM Users
Other tested functions will insert/update table Users too.
I searched on internet, several people said it's MS bug, anybody help?
View 1 Replies
View Related
