Help Me! Convert SQL Syntax To LINQ With Multi Join
May 5, 2008
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 a
Left Join
(Select c.TeamID,Count(*) as G from SportMedals b
Inner Join SportAthletes c On b.AthleteID = c.AthleteID
Where Medal = 'G'
Group By c.TeamID) g On a.TeamID = g.TeamID
Left Join
(Select c.TeamID,Count(*) as S from SportMedals b
Inner Join SportAthletes c On b.AthleteID = c.AthleteID
Where Medal = 'S'
Group By c.TeamID) s On a.TeamID = s.TeamID
Left Join
(Select c.TeamID,Count(*) as B from SportMedals d
Inner Join SportAthletes c On d.AthleteID = c.AthleteID
Where Medal = 'B'
Group By c.TeamID) b On a.TeamID = b.TeamID
Order 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)
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.
Hi Guys, I started working with linq and vb9.0 but i have a small problem i could feagure how to solve in c# but not in vb I wanted to make left join or right join on vb 9.0 and linq is it possible or this is only c# feature ? Waiting to hear from u guys, Thanks Softy
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
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.
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?
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.) THANKS
I created a report in Reporting Services 2005 where I added multi-value parameters. When I run my report, and try to select more than one parameter, I get an error: Incorrect syntax near ','
This query is the first time I am using the Unpivot syntax and I am coming across a problem. When I try to unpivot my data, I get the following statement:
"Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "Table3.DocketId" could not be bound."
What is the cause of this issue?
Select Table3.DocketId, UP.AssignmentType, Up.AssignedStaff From ( Select distinct Table2.DocketId,
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".
Could someone please help me!!! I am using a multi-value parameter in SQL 2005 reports and am getting the following error message:
An error has occured during report processing. Qiery execution failed for dataset an expression of non-boolean type specified in a context where condition is expected, near ',' Incorrect syntax near keyword else.
The multi-value parameter works when it isn't run in the if, else clause i checked the where clause with a single paramter and it works OK. I don't understand what is causing this problem but I really need to fix it. Here is my query.
if @job_SubRep_ProjNo_param = '0'
Begin select Job_Job_No as job, Job_Job_Name as Job_title, cast(Job_Total_Fee as float) as fee, employee_first_name + ' ' + employee_surname as jl_name, cast(Job_Percent_Complete as float) as percentcomplete, cast(Job_Work_Done as float) as workdone, cast(Job_Invoicing as float) as job_Invoicing, job_WIP, Job_Expenditure as timecost, job_project_no, Job_Profit_Loss, cast(Job_Hours as float) as hours, job_expenses from job_tbl inner join project_tbl on job_project_no = project_no inner join employee_tbl on job_jl_empno = employee_no where project_pl_empno = @pl_employeeNo or job_jl_empno = @jl_subRep
end else select Job_Job_No as job, Job_Job_Name as Job_title, cast(Job_Total_Fee as float) as fee, employee_first_name + ' ' + employee_surname as jl_name, cast(Job_Percent_Complete as float) as percentcomplete, cast(Job_Work_Done as float) as workdone, cast(Job_Invoicing as float) as job_Invoicing, job_WIP, Job_Expenditure as timecost, job_project_no, Job_Profit_Loss, cast(Job_Hours as float) as hours, job_expenses from job_tbl inner join project_tbl on job_project_no = project_no inner join employee_tbl on job_jl_empno = employee_no where job_project_no in (@job_SubRep_ProjNo_param) or job_jl_empno = @jl_subRep
hi,consider this:i have american users in a db called "user" in a server called AMERICAand rest of the world users in a db called "user" in a server called OTHERhow can i do a join betwhen American's users and Rest of the word's User db?
I want only one row for each contact that contains the most recent calendar as enddate and most recent history as ondate. I'm getting multiple rows for each matching contact.
SELECT DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) AS Date_Checked_In, COUNT(*) AS No_Files FROM tblFiles WHERE (CONVERT(datetime, LastDateIn, 103) >= DATEADD(day, DATEDIFF(day, 10, GETDATE()), 0)) GROUP BY DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) ORDER BY DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) DESC
I want Date_Checked_In not to show the time. I have tried using convert on the 1st line to just show the date but keep getting a syntax error.
Code Block @LegalCounsel int = 0, @ClientID int = 0, @FundID int = 0
DECLARE @thisQuery as varchar(max)
SET @thisQuery = 'SELECT p.Portfolio, SeniorMgr, fl.Legal FROM FundClient f' BEGIN SET @thisQuery = @thisQuery + ' INNER JOIN FundPortfolio p ON p.ClientID = f.ClientID' END BEGIN SET @thisQuery = @thisQuery + ' LEFT OUTER JOIN Staff s ON (s.ClientID = p.ClientID AND s.FundID = p.PortfolioID AND s.ClientID = f.ClientID)' END BEGIN SET @thisQuery = @thisQuery + ' LEFT OUTER JOIN myLegal l ON (l.ClientID = p.ClientID AND l.FundID = p.PortfolioID) INNER JOIN FullLegalList fl ON fl.LegalID = l.LegalCounsel' END BEGIN IF @Legal != 0 SET @thisQuery3 = @thisQuery3 + ' WHERE rl.Legal = ' + cast(@LegalCounsel as varchar(11)) END BEGIN IF @ClientID != 0 SET @thisQuery = @thisQuery + ' AND p.ClientID = ' + cast(@ClientID as varchar(11)) END BEGIN IF @FundID != 0 SET @thisQuery = @thisQuery + ' AND p.PortfolioID = ' + cast(@FundID as varchar(11)) END BEGIN SET @thisQuery = @thisQuery + ';' END
I have three tables A, B, and C. What i want to do is basically left join A with B, then left join B with C. However when try to do this it won't work out as i imagined. Is there a better way (one that works) for accomplishing what i am trying to do?
Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S
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
I'm writing a query where I have multiple left-outer joins but I keep getting multi-part identifier error. See the query below?
SELECT gl.seg5 Natural ,gl.seg2 Office ,gl.seg3 Dept ,gl.seg4 Team ,gl.seg6 Sub ,gl.seg7 Tkpr ,gl.seg1 Comp ,'CHK' Source
[Code] ....
Errors
Msg 4104, Level 16, State 1, Line 68 The multi-part identifier "csddt.baid" could not be bound. Msg 4104, Level 16, State 1, Line 68 The multi-part identifier "csddt.cknum" could not be bound. Msg 4104, Level 16, State 1, Line 68 The multi-part identifier "csddt.ckline" could not be bound.
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'
