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.
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?
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".
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?
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 ?
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
Erland 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
I 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
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.
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'
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?
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'
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
I 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
I 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
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?
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.
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.
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
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.
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?
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.
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring")) Dim cmdSQL As New SqlCommand(strSQL, myConnection)
) ) ) ) ) AS timeType, Sum([2007_hours].Hours) AS SumOfHours from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql): select ID, FName, LName if(SUBSTRING(FirstName, 1, 4)= 'Mike') Begin Replace(FirstNam,'Mike','MikeTest') if(SUBSTRING(LastName, 1, 4)= 'Kong') Begin Replace(LastNam,'Kong,'KongTest') if(SUBSTRING(Address, 1, 4)= '1245') Begin ......... End End
end
Case Statement might be the solution but i could not do it.
