Is there a way to join 2 tables from different databases on the same instance? If so, what is the syntax? For example I have an instance called TEST with 2 databases (DB1 and DB2). Each database have a table (DB1.TABLE & DB2.TABLE). Both tables have a common column called ID.
Can both tables be joined in a query?
TIA.
Hello
I am trying to join 2 tables listing product names but I can’t get the syntax quite right.
I have managed to get the 2 tables to display separately.
Table #1:
Select
ProductName as [Product Name]
From
Product_CatA
Where Auto = 'yes'
Order By
ProductName ASC
Table #2
SELECT ProductName, Price, '<a href="' + url + '">' + Title + '</a>' as Link
FROM ProductNames
WHERE Extn = '0151'
And url like 'http:%'
ORDER BY ProductName ASC
I want to display all the data values taken from Table #1 as above.
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.
Thanks
Monitoring a sql 2000 server where a vendor app is running. Looking at a poor performing sql and I saw the following in the where clause
WHERE immunization_mast_.account_id =* p.account_id AND
immunization_mast_.case_id =* p.case_id
What is the meaning of the =*, have not seen this before?
Hi
I have two databases on my server, I need a simple query with one join between one table from each database.
I looked in the help of FROM clause and found the Argument "table_source" where it explains this :
"If the table or view exists in another database on the same computer running Microsoft® SQL Server™, use a fully qualified name in the form database.owner.object_name".
Can someone please help me fill the variants ??
My DB name is "Forum" the owner is "DBRNDAdministrator" and the table name is "TblUsers", so I tried to write in the FROM clause :
"FROM Forum.DBRNDAdministrator.TblUsers" but it doesn't work... so anyone have any idea how should it be ?
Thanks,
Inon.
Hello,
I need to write and Ansi Outer Join that has 2 columns from the same table.
I keep getting
Server: Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dim_person.person_key" could not be bound.
here is what the code looks like. any help is appreciated.
select ...
from dim_day_of_year
, dim_company
, dim_currency
, dim_account
, dim_person
, ods_conference
left outer join fact_usage_alloc fua1 on ods_conference.conf_key=fua1.conf_key
left outer join fact_usage_alloc fua2
on dim_person.person_key=fua2.requestor_person_key
where...
I am trying to figure out some sql syntax, and I could use some help. Thisis my first atempt at joins, so bear with me.I have a table (A) which looks like the followingID Data Source-------------------------1 abcdef 1002 abcdef 1003 abcdef 2004 abcdef 2005 abcdef 200A second table (B) which looks like the followingKey ID------------------------Key1 1Key1 2Key1 3Key1 4Key2 1Key2 2Essentially, A is a table of items, and B is a table of where those itemshave been used (Key1 is like an invoice which has items 1-4 on it, Key2 is asecond invoice with 1 and 2.) Source, in table A, is like the itemsupplier.I would like to get a list of every invoice (Key) that has used a part (ID)from a particular Source.So, for example, I would like to query for source 100 and get back (Key1,Key2) or query for source 200 and get back only Key1.To this end, I tried"SELECT DISTINCT B.Key FROM B JOIN A ON (B.ID = A.ID) WHERE (A.Source =100)"But I got an empty recordset, so something is amiss.Any help is greatly appreciated.Thanks,-d
View 2 Replies View RelatedErland Sommarskog <sommar@algonet.se> wrote in messagenews:Xns93EFA9C57954AYazorman@127.0.0.1...[color=blue]> MAB (fkdfjdierkjflafdafa@yahoo.com) writes:[color=green]> > What I want is the sum of the amounts of the last payments of all> > customers. Now the last payment of a customer is not necessarily the one> > with the highest paymentid for that customer BUT it is the one with the> > highest paymentid on the MOST RECENT date. We dont keep the time part> > just the date so if there are more than 1 payments of a customer on a> > date ( and there are many such cases ) only then the paymentid decides> > which is the last payment. Further the last payment may be the last as> > of today but I may want to find the sum of all the last payments upto> > say March 1, 2003 or any date. My own solution is too slow even it is> > correct.[/color]>> This solution is not tested, as you did not provide any sample data:>> SELECT SUM(p3.amount)> FROM Payments p3> JOIN (SELECT paymentid = MAX(p2.paymentid)> FROM Payments p2> JOIN (SELECT p1.customerid, mostrecent = MAX(p1.date)> FROM Payments p1> WHERE p1.date <= '20030301'> GROUP BY p1.customerid) AS p1> ON p1.customerid = p2.customerid> AND p1.mostrecent = p2.date) AS p2> ON p3.paymentid = p2.paymentid>> This solution is for SQL Server only. I don't know Access, so I can't> help with that.>> As for performance, this is likely to be a case of finding the best> indexes. Clustered on (date, customerid) and nonclustered in (paymentid)> maybe.[/color]Thanks. This looks fascinating. It looks correct too (although I haventfully verified that). From this my next questionIs it possible to write your query in older join syntax likeFROM Payments p1, Payments p2WHERE p1.customerid = p2.customerid etc.Or is it that the newer syntax is superior such that you can do things withit that you cant do with the older one?I cant see how to write such a query the older way because you have only oneSELECT Clause thereMany thanks again
View 4 Replies View RelatedI have a query where I need to join a table to multiple tables and alias a field from those tables on each join. I tried the syntax below but received a error. Please assist, first time trying to do this.
JOIN dbo.AbsenceReason ar ON ar.AbsenceReasonID = sda.AbsenceReasonID
WHERE ar.[Name] = 'DailyReason'
LEFT JOIN dbo.AbsenceReason ON ar.AbsenceReasonID = spa.AbsenceReasonID
WHERE ar.[Name] = 'PeriodReason'
LEFT JOIN dbo.AbsenceReason ON ar.AbsenceReasonID = cio.AbsenceReasonID
WHERE ar.[Name] = 'CheckInOutReason'
error I receive is :
Msg 156, Level 15, State 1, Procedure p_000001_GetAttendanceProfileData, Line 45
Incorrect syntax near the keyword 'LEFT'.
Msg 102, Level 15, State 1, Procedure p_000001_GetAttendanceProfileData, Line 63
Incorrect syntax near ')'.
Hi guys, I'm still new around here and still a noob for sql. Can you give me some example for some joins from the easy ones to the most complex and all of it kinds, I heard that there's a lot of different kind of join in sql. You can give me some link or maybe some code examples for me, I appreciate all kinds of help . Thanks for all your help. Regards.
View 3 Replies View RelatedHi,
Can anyone please tell me how can i convert the following query in
the old join syntax to the new join syntax so that it returns the same
results? I tried converting it, but it returns different reuslts.
please help me as it is urgent.
OLD Syntax:
===========
SELECT
....................
..<column_list>......
FROM
dbo.ASSET_BOOK AL1,
dbo.ASSET_BOOK AL2,
dbo.ASSET_HEAD AL3,
dbo.ASSET_BOOK AL4,
dbo.ASSET_BOOK AL5
WHERE
(AL1.U##ASSETNO =* AL3.U##ASSETNO
AND AL3.U##ASSETNO *= AL2.U##ASSETNO
AND AL4.U##ASSETNO =* AL3.U##ASSETNO
AND AL3.U##ASSETNO *= AL5.U##ASSETNO)
AND
(AL1.U##BOOKCODE = 'USGAAP'
AND AL2.U##BOOKCODE = 'CONSUSD'
AND AL4.U##BOOKCODE = 'ICP'
AND AL5.U##BOOKCODE = 'STETC'
)
New Syntax:
===========
SELECT
COUNT(*)
FROM dbo.ASSET_BOOK AL1
RIGHT JOIN dbo.ASSET_HEAD AL3
on AL1.U##ASSETNO = AL3.U##ASSETNO
LEFT JOIN dbo.ASSET_BOOK AL2
on AL3.U##ASSETNO = AL2.U##ASSETNO
RIGHT JOIN dbo.ASSET_BOOK AL4
on AL4.U##ASSETNO = AL3.U##ASSETNO
LEFT JOIN dbo.ASSET_BOOK AL5
on AL3.U##ASSETNO = AL5.U##ASSETNO
AND
AL1.U##BOOKCODE = 'USGAAP'
AND AL2.U##BOOKCODE = 'CONSUSD'
AND AL4.U##BOOKCODE = 'ICP'
AND AL5.U##BOOKCODE = 'STETC'
Thanks in advance.
Rafi
Hi,
Trying to join two tables together.
Select * from ...
inner join
on table1.ID = table2.ID;
this works fine, however i want to use the RIGHT function so that i get the last four digits of the ID. this causes a parse error:
inner join on table1.right([ID],4) = table2.right([ID],4);
searched a bit but couldn't find a way to do what im trying to accomplish. is there a proper syntax?
thanks!
I am getting the error message:
Msg 156, Level 15, State 1, Line 62
Incorrect syntax near the keyword 'inner'.
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near 'Code'.
When I attempt to run the following query:
select NewUsed, VehicleYear, VehicleMake, VehicleModel, VehicleTrim,
AppZip, ltrim(newtrademake) AS TradeMake, ltrim(trademodel)TradeModel,
TradeYear, NcodeL AS TradeNcodeL, NcodeM AS TradeNcodeM, OwingOnTrade, TradeAllowance, NetTradeIn
from
(select *,
Case
When TradeMake = ' Ford' and (Trademodel like '%Aerostar%' or Trademodel like '%Bronco%')
Then 'FORD TRUCKS'
Else ltrim(TradeMake)
End
AS NewTradeMake
from dbo.vw_dds)
inner join (select distinct make, model, ncodel, ncodem, modelyear from us.dbo.algmaster) Code
on code.make = ltrim(newtrademake) and code.model= ltrim(trademodel) and code.modelyear = tradeyear
The error is related to the inner join but I can't seem to figure out what I am doing wrong.
Thank you for your help,
Tasha
I inherited a query and I am getting an error of Unsupported literal in join in the INNER JOIN FRDM.dbo.MEMBER_SUBSC FRDM_dbo_MEMBER_SUBSC2
ON (frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.SBSB_CK = FRDM_dbo_MEMBER_SUBSC2.SBSB_CK AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX
= '00')statement. Specifically the AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX
= '00')part.
SELECT frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.GRGR_ID AS 'group number'
[code]....
what is the syntax to join a table with the result of antoher query.For example i have two tablesCreate Table Customers (CustomerID int,LastPaymentDate Datetime )Create Table Payments (PaymentID int,CustomerID int,PaymentDate Datetime )What query will bring me the customers whose lastpaymentdate in thecustomers table is not correct.That can only be checked by comparing it with the max paymentdate for eachcustomer in the payments table.I want this to be done by ansi standard sql. Not using any specific featureof sql server.thx
View 11 Replies View RelatedI am now working on SQL Server 2000 having had previous experience ona different database. Both of the OUTER JOIN syntaxes is differentfrom what I am used to and I am finding it slightly confusing.For example, given two tables :wipm_tbl_miwipm_tbl_wi (which may not have data in it for a specific record thatexists in the first table.)If I use the old style syntax :SELECT mi.workitemid, wi.datavalueFROM wipm_tbl_mi mi , wipm_tbl_wi wiWHERE mi.workitemid *= wi.workitemidAND mi.workitemid = 1AND wi.dataname = 'XXX'I get back1,NULLwhen there is no matching record in wipm_tbl_wi, which is what Iexpect.However, if I try to use the SQL-92 syntaxSELECT mi.workitemid, wi.datavalueFROM wipm_tbl_mi miLEFT OUTER JOIN wipm_tbl_wi wiON mi.workitemid = wi.workitemidWHERE mi.workitemid = 1AND wi.dataname = 'XXX'I don't get anything back. Please can someone help me understand whatis wrong with the bottom query.Thank you,Martin
View 2 Replies View RelatedI apologize if this has been asked before- I searched google but couldnot find a concrete answer.I recently inherited a database whose t-sql code is written in a formatthat I find difficult to read (versus the format I have used foryears).I have tested the queries below using the SQL Profiler, and both haveidentical costs. Is there any advantage of one format over the other?Format 1:---------SELECT *FROM Customers c, Orders oWHERE c.CustomerID = o.CustomerIDFormat 2:---------SELECT *FROM Customers cINNER JOIN Orders AS o ON c.CustomerID = o.CustomerIDIs it just a matter of personal preference? Does the same hold true forusing OUTER JOIN versus the old *= or =* ?Thanks,Matt
View 3 Replies View RelatedTeam,
Our product was written against an older version of SQL and the non-ANSI standard join operators (*= and =*). Our group is now looking to move our database to SQL Server 2005.
We are now faced with updating a lot of legacy queries that use this method of joins and I was hoping for a shove in the right direction.
I have run across a query similar to the following:
SELECT <many fields go here>
FROM a, b, c, d, e, f, g, h
WHERE a.fld1 *= d.fld1
AND e.fld3 *= h.fld3
The query, as it is with the non-ANSI join operators, returns a single record. For the life of me, I cannot figure out how to format the FROM statement to create the LEFT OUTER JOIN statement(s) to get the query to work.
The SQL Server 2005 books online seems to be lacking in the area of examples where you have multiple joins using different tables; they focus on one table joining against multiple tables instead.
Anyone have any reading suggestions or other ideas on how to get this to work?
Thanks.
Richard
I'm looking at upgrading to SQL2005 and have found the following issue with some old t-sql we have.
We have some views that are created using the old outer join syntax of *=. If I run the SQL in query analyzer, it runs fine...but if I create a view with the SQL, when I query the view I get the error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I tried changing the database compatibility level all the way back to 7.0, and recreating the view (still using *=), but still had the same error.
Is it possible to have a view that uses this old syntax with SQL2005? How? I don't want to have to find/rewrite everything very quickly.
thanks,
Matt
HelloI am seeing the following syntax error: Error on Primary Select: Line 4: Incorrect syntax near '='.When I try to join two tables see my code below:------------------------------------------------------------------------------------------------------------------------ SELECT a.ProdName as [Product Name], b.price,b.link FROMProd_CatA a LEFT JOIN (SELECT ProdName, Price, '<a href="' + url + '">' + Title + '</a>' as Link FROM ProdNames WHERE = url like 'http:%' ) b ON a.ProdName=b.ProdNameWhere a.Red = 'yes'ORDER BY a.ProdName ASC
------------------------------------------------------------------------------------------------------------------------------ I unable to find a solution. I would be grateful if somebody could please advise.ThanksLynn
Having problems rewriting my join condition using the "inner join" syntax.
My query, working with an intersection table:
SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u, Accounts_UserRoles ur
WHERE
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID
This works fine, but i want to write it using 'inner join' style, so I tried:
SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u
INNER JOIN Accounts_UserRoles ur
ON
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID
which gives me an error (The column prefix 'r' does not match with a table name or alias name used in the query.)
Any ideas as to how I'm screwing this up would be appreciated.
Thanks,
Gordon Z
What is wrong with my syntax?I want to return the value of the AchiveYear Value based on records in theCall that match.
SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch ON tblCall.DATEPART(yyyy,Call_Date) = Arch.ArchiveYear
[code]...
Hi guys,
How can I translate this join into new syntax?
SELECT S.Dr_Code
FROM GJCSite S , GJCJob_Det JD, GJCSub_Job SJ
WHERE S.Com_Code = SJ.Com_Code (+)
AND JD.Sub_Job_Code = SJ.Sub_Job_Code (+)
AND JD.Job_Code = SJ.Job_Code (+)
Cheers,
Hi...
I have 3 tables:SportTeams (TeamID, TeamName)SportAthletes (TeamID, AthleteID, AthleteName)SportMedals (AthleteID, Medal)
I want to have a brief medal list (TeamID, G, S, B). I can write query in systax:
Select a.TeamID, a.TeamName_en,g.G,s.S,b.B from SportTeams aLeft Join(Select c.TeamID,Count(*) as G from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'G'Group By c.TeamID) g On a.TeamID = g.TeamIDLeft Join(Select c.TeamID,Count(*) as S from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'S'Group By c.TeamID) s On a.TeamID = s.TeamIDLeft Join(Select c.TeamID,Count(*) as B from SportMedals d Inner Join SportAthletes c On d.AthleteID = c.AthleteIDWhere Medal = 'B'Group By c.TeamID) b On a.TeamID = b.TeamIDOrder By g.G desc, s.S desc, b.B desc, a.TeamID asc
But I can't write it in LINQ syntax (I am beginner with C#, LINQ)
Can you help this sample to LINQ systax?
Thanks!
I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well.I get a invalid expression error when I run the procedures in Power Builder. We have previously been using a lower database compatibility model in 2005, which allowed the procedures to work there. I have learned that =* is the old way to write right outer joins.
                           For example:
                           select  *
                           from   A
                           right outer join
                               B
                          Â
[code]...
Is my approach correct, or do I need to add a WHERE condition to it? I don't have access to the production database to check. The development database is in SQL Server 2012 too, sO I will not be able to run the old version there to check.
Hello all--
I'm trying to run a SELECT on 3 tables: Class, Enrolled, Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I know
to be incorrect. Furthermore, it appears that the counts are being
multiplied together (in one instance, enrolled should be 14, waiting
should be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts are
accurate. The problem only occurs when I try to pull counts from both
the tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
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.
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.
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!
A question for everyone:
With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax.
We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code.
Thanks, in advance,
Brian
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".
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] ....