SQL Query Joins

[afrosheen]

Can anyone attempt to help me on why this query fails on the 2nd and 3rd joins?

SELECT schedheader.DATE,
schedheader.EXCEPTION_CODE,
schedheader.START_MINUTE,
schedheader.LENGTH,
scheddetail.EXCEPTION_CODE,
scheddetail.START_MINUTE,
scheddetail.LENGTH,
schedheader.CUSTOMER_ID,
[except].EXC_NAME
FROM schedheader LEFT OUTER JOIN scheddetail ON schedheader.EXCEPTION_CODE = scheddetail.EXCEPTION_CODE and
schedheader LEFT OUTER JOIN schedheader ON schedheader.EXCEPTION_CODE = [except].EXC_ID and
scheddetail LEFT OUTER JOIN scheddetail ON scheddetail.EXCEPTION_CODE = [except].EXC_ID
WHERE (((schedheader.CUSTOMER_ID)=2));

Query Analyzer shows
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'LEFT'.
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'schedheader'.

However have been unsuccessfull in several variations to solve this.

Thanks!

 

[KeithG]

FROM (((schedheader LEFT OUTER JOIN scheddetail ON schedheader.EXCEPTION_CODE = scheddetail.EXCEPTION_CODE)LEFT OUTER JOIN schedheader ON schedheader.EXCEPTION_CODE = [except].EXC_ID)LEFT OUTER JOIN scheddetail ON scheddetail.EXCEPTION_CODE = [except].EXC_ID)

try this FROM Clause I think you have an error with your joins.

 

[afrosheen]

Now I'm up to this error...

Server: Msg 1013, Level 15, State 1, Line 10
Tables or functions 'schedheader' and 'schedheader' have the same exposed names. Use correlation names to distinguish them.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'schedheader'.

when using

SELECT schedheader.DATE,
schedheader.EXCEPTION_CODE,
schedheader.START_MINUTE,
schedheader.LENGTH,
scheddetail.EXCEPTION_CODE,
scheddetail.START_MINUTE,
scheddetail.LENGTH,
schedheader.CUSTOMER_ID,
[except].EXC_NAME
FROM (((schedheader LEFT OUTER JOIN scheddetail ON schedheader.EXCEPTION_CODE = scheddetail.EXCEPTION_CODE) LEFT OUTER JOIN schedheader ON schedheader.EXCEPTION_CODE = [except].EXC_ID) LEFT OUTER JOIN scheddetail ON scheddetail.EXCEPTION_CODE = [except].EXC_ID)
WHERE (((schedheader.CUSTOMER_ID)=2));

 

[RayH]

I may have gotten the wrong end of the stick here but don't you need a join to the except table?

FROM schedheader
LEFT OUTER JOIN scheddetail on
schedheader.EXCEPTION_CODE = scheddetail.EXCEPTION_CODE
LEFT OUTER JOIN [except] ON
schedheader.EXCEPTION_CODE = [except].EXC_ID and scheddetail.EXCEPTION_CODE= [except].EXC_ID

WHERE schedheader.CUSTOMER_ID=2;

 

[afrosheen]

That was my thoughts also see the attachement and on page 13 and 14 is where this is coming from. Which shows the originial query in my 1st posting. So I'm not sure how this could have worked, I'm thinking there is a typo somewhere in referring the loops and except table.

As always queries never perform like they are suppose to

 

[byronavory]

I know that this is a relatively old post, but I am actually attempting to build this same query. I was wondering if you every worked it out?

 

[RayH]

WOW, FIVE YEARS!! Has it been this long, I had almost forgotten about this!

I have no idea whether it worked for him or not but this section using from statement in my post should have returned something. The original code does appear, to me at least, to have a typo like he said.

If the code is expanded it reveals two orphaned 'AND' statements. Remove them and it should work, though i do not know what the original authors were intending to use in those clauses.

FROM schedheader LEFT OUTER JOIN scheddetail 
ON schedheader.EXCEPTION_CODE = scheddetail.EXCEPTION_CODE 
-- ORPHANED 'AND'
and schedheader 

LEFT OUTER JOIN schedheader

ON schedheader.EXCEPTION_CODE = [except].EXC_ID
-- ORPHANED 'AND"
 and scheddetail 
 
 LEFT OUTER JOIN scheddetail 
 ON scheddetail.EXCEPTION_CODE = [except].EXC_ID
[CODE]