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)

Can you help this sample to LINQ systax?

Thanks!

View 5 Replies


ADVERTISEMENT

Question: Has Anyone Written A Tool To Convert From ANSI-89 To ANSI-92 Join Syntax?

Oct 25, 2007

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

View 7 Replies View Related

Left Join Using Vb 9.0 And LINQ

Dec 2, 2007

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

View 2 Replies View Related

Help With Multi Join Or Multi Tier Select.

Jul 20, 2005

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

View 5 Replies View Related

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

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

Multi-table JOIN Query With More Than One JOIN Statement

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

Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?

Mar 31, 2008

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!

View 4 Replies View Related

How Would You Convert A Hash Join Into A Merge Join?

May 6, 2008

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

View 3 Replies View Related

Incorrect Syntax Near ',' Using A Multi-value Parameter

May 1, 2007

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 ','

View 1 Replies View Related

T-SQL (SS2K8) :: Unpivot Syntax And Multi-part Identifiers

Jan 29, 2015

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,

[Code] ....

View 3 Replies View Related

Error Incorrect Syntax Near ',' When Exec Sql With Multi Value Parameter

Apr 3, 2008

I already used temptable, it works fine in SQL Server but doesn't work in Visual Studio. (it returns me "incorrect syntax near ',').

Here is my sql:


SET @sql = 'SELECT
RMI.Response_Date,
RMI.Master_Incident_Number,
RMI.Jurisdiction,
RVA.Radio_Name,
RVA.Response_Number,
RMI.Division,
RMI.Battalion,
RMI.Address,
RMI.Call_Disposition,
RMI.Cancel_Reason,
RMI.Problem,
RMI.Time_CallEnteredQueue,
RVA.Time_Enroute,
RVA.Time_ArrivedAtScene,
RG.Region,
RN.District ' +
' INTO RPT_PRIME_JOB_DTL ' +
' FROM ' + @server_name + 'Response_Master_Incident RMI ' +

'LEFT OUTER JOIN ' + @server_name + 'Response_Vehicles_Assigned RVA
ON RMI.ID = RVA.Master_Incident_ID ' +

'LEFT OUTER JOIN AKSDCSVR01.CAD_Lookup_Tables.dbo.Radio_Name RN
ON RVA.Radio_Name = RN.Radio_Name Collate SQL_Latin1_General_CP1_CI_AS ' +

'LEFT OUTER JOIN AKSDCSVR01.CAD_Lookup_Tables.dbo.Region RG
ON RMI.Jurisdiction = RG.Jurisdiction Collate SQL_Latin1_General_CP1_CI_AS ' +

'WHERE ' +
' RG.Region = ''' + @region + '''' +
' AND RN.District IN (SELECT Item FROM dbo.StringArrayIntoTable(''' + @district + ''', '','') ' +
' AND (RMI.response_date >= ''' + CONVERT(VARCHAR(10), @date_from,111) + '''' +
' AND RMI.response_date <''' + CONVERT(VARCHAR(10),@date_to+1,111) + ''')' +
' AND RVA.Radio_Name LIKE ''PD%'''

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXEC (@sql)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT *
FROM RPT_PRIME_JOB_DTL


Thank

View 3 Replies View Related

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans

Apr 16, 2008



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

View 5 Replies View Related

Convert Int Data Into A Multi-field String

Jan 9, 2007

Trying to get a multi field grouping to print.

Client + Req Number + Status

Client and status are nvarchar fields

Req Number is an int.

Keep getting # Error for this field on the report. on the report.

It works in Access - should it be this difficult?

Thanks!

Terry

View 4 Replies View Related

Multi-Value Parameter Error: Incorrect Syntax Neat The Keyword Else

Dec 11, 2007

Hi all,

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

Thanks in advance!!

Katie

View 7 Replies View Related

Join Multi Server

Oct 9, 2006

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?   

View 3 Replies View Related

Multi-Table Join Help

Feb 7, 2002

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 c1.contact ,ca.enddate ,ch.ondate

from ca

join c1 on ca.accountno = c1.accountno

join ch on ca.accountno = ch.accountno

where ca.ondate in (select max(ondate) from ca group by accountno)
------------------------------------------

View 1 Replies View Related

Multi Database Join

Apr 8, 1999

Is it possible to create a join between two SQL Server DB's on the same server? What is the syntax?

View 1 Replies View Related

CONVERT And Syntax

Jan 22, 2007

Here's my sp

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.

View 5 Replies View Related

Multi Join Stored Proc

Nov 20, 2007

Hi,
Why does the below not return any results???
Colorcodes mark the related keys.

Thanks.


Tables:
FundClient (ClientID PK, Client)

FundPortfolio (PortfolioID PK, Portfolio, ClientID FK)

Staff(StaffID PK, SeniorMgr, ClientID FK, FundID FK)

myLegal(myID PK, LegalCounsel FK, ClientID FK, FundID FK)

FullLegalList(LegalID PK, LegalName)





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





View 12 Replies View Related

Multi-table Join Problem

Jan 9, 2008

Hello All,

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?

Any help is much appreciated.

Thanks,

View 7 Replies View Related

Convert(datetime, Order_date) Syntax

Nov 11, 2002

Hi,

select first_name,last_name, order_date
from orders where convert (datetime, order_date) > '11/08/02'

I get the following error while executing this on sql server 2000.

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

This statement wokrs with 7.0. Also I haven't come across anything that tells me that this syntax is not supported in sql server 2000.

Then why am I getting this error? Is there any database setting that needs to be changed? How can I make this statement work in 2000?

Thanks,
Shaili

View 3 Replies View Related

Convert Access Query To Syntax?

Aug 21, 2013

I am trying to convert a code from access Db to sql code?

II(Left([dbo_ClaimLosstype].[dscr],4)="Liab","D_Liab","D_Property"),

View 4 Replies View Related

Ambiguous Column Names In Multi-Table Join

Jun 21, 2006

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

View 6 Replies View Related

Bad Join/where Syntax

Mar 29, 2004

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?

View 2 Replies View Related

Need Syntax Help In Join From Two DB

Jun 15, 2004

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.

View 4 Replies View Related

Out Join Syntax

Jul 25, 2007

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...

View 9 Replies View Related

Join Syntax Help

Jul 23, 2005

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 Related

SQL JOIN SYNTAX

Jul 20, 2005

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

View 4 Replies View Related

Help With Join Syntax

Jun 29, 2006

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

Incorrect syntax near ')'.

View 12 Replies View Related

Left Outer Join / Multi-part Identifier Error

Jul 22, 2014

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.

View 5 Replies View Related

Need Help On Complex Join Syntax ???

Mar 3, 2008

 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 Related

New Outer Join Syntax

Aug 19, 2002

Hi,

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved