SQL: JOIN syntax error

Question

I am attempting to eliminate unwanted duplicate query results. The gist is that the field [CUSIP] exists in all tables in question, however, the field [4DTYR] exists in all tables except [IDX_FS].

I had previously only joined the tables via the [CUSIP] field, and that resulted in the query produced unwanted duplicate results (some sort of a permutation of [4DTYR] from all the tables that contained that field).

Then, I made the modification below. However, now I'm receiving a JOIN syntax error. Can anyone kindly help? Thanks!

FROM 

(((IDX_FS LEFT JOIN DATA_BS 
  ON IDX_FS.CUSIP = DATA_BS.CUSIP) 

LEFT JOIN DATA_Footnotes 
  ON IDX_FS.CUSIP = DATA_Footnotes.CUSIP) 

LEFT JOIN DATA_IS 
  ON IDX_FS.CUSIP = DATA_IS.CUSIP) 

LEFT JOIN DATA_SP 
  ON IDX_FS.CUSIP = DATA_SP.CUSIP 

AND (((DATA_BS LEFT JOIN DATA_IS 
  ON DATA_BS.CUSIP = DATA_IS.CUSIP 
     AND DATA_BS.4DTYR = DATA_IS.4DTYR) 

LEFT JOIN DATA_SP 
  ON DATA_BS.CUSIP = DATA_SP.CUSIP 
     AND DATA_BS.4DTYR = DATA_SP.4DTYR) 

LEFT JOIN DATA_Footnotes.4DTYR 
  ON DATA_BS.CUSIP = DATA_Footnotes.CUSIP 
     AND DATA_BS.4DTYR = DATA_Footnotes.4DTYR

Hi, just tried reformatted to the best of my abilities. I'm really new to this! — user2898577
– user2898577, Commented Oct 19, 2013 at 23:47
You have already posted this and subsequently this. Please see this for further info. — user806549
– user806549, Commented Oct 20, 2013 at 0:29

user806549 · Accepted Answer · 2013-10-19 23:40:39Z

1

It appears that you have one ( too many:

FROM 
     (
      (
       (IDX_FS LEFT JOIN DATA_BS ON IDX_FS.CUSIP = DATA_BS.CUSIP) 
       LEFT JOIN DATA_Footnotes ON IDX_FS.CUSIP = DATA_Footnotes.CUSIP
      ) 
      LEFT JOIN DATA_IS ON IDX_FS.CUSIP = DATA_IS.CUSIP
     ) 
     LEFT JOIN DATA_SP ON IDX_FS.CUSIP = DATA_SP.CUSIP AND 
     ( -- This is unmatched
      (
       (DATA_BS LEFT JOIN DATA_IS ON DATA_BS.CUSIP = DATA_IS.CUSIP AND DATA_BS.4DTYR = DATA_IS.4DTYR)     
       LEFT JOIN DATA_SP ON DATA_BS.CUSIP = DATA_SP.CUSIP AND DATA_BS.4DTYR = DATA_SP.4DTYR
      ) 
      LEFT JOIN DATA_Footnotes.4DTYR ON DATA_BS.CUSIP = DATA_Footnotes.CUSIP AND DATA_BS.4DTYR = DATA_Footnotes.4DTYR
     -- A ) here perhaps?

answered Oct 19, 2013 at 23:40

user806549

Sign up to request clarification or add additional context in comments.

3 Comments

user2898577 Over a year ago

Thanks! I tried your edit and now i'm getting a missing operator error?

user806549 Over a year ago

There is something more wrong before the unmatched parenthesis, I guess. Should the ANDreally be there?

user2898577 Over a year ago

I'm such a noob that I can't tell! I'm trying it without the AND now.

Deepshikha · Accepted Answer · 2013-10-20 11:56:28Z

In the FROM phase we specify table or tables that need to be queried, and if table operators are specified, this phase processes those operators from left to right. Join (Left / Right) are table operators and each table operator operates on one or two input tables and returns an output table. The result of a table operator is used as the left input to the next table operator—if one exists—and as the input to the next logical query processing phase otherwise. In your query i find two issues:

DO you want entire 'AND' condition(Line 15) to be added as a filter condition in ON clause when left joining with DATA_SP?
In Last line you are trying to specify a left join on field [4DTYR] of DATA_Footnotes table which is not right as the Left join operator expects a table to be supplied.

I think a pseudo like below might help:

select IDX_FS.CUSIP , DATA_BS.[4DTYR] 
FROM 
  IDX_FS
  LEFT JOIN DATA_BS 
  ON IDX_FS.CUSIP = DATA_BS.CUSIP
  LEFT JOIN DATA_Footnotes 
  ON IDX_FS.CUSIP = DATA_Footnotes.CUSIP AND DATA_BS.CUSIP = DATA_Footnotes.CUSIP  AND DATA_BS.[4DTYR] = DATA_Footnotes.[4DTYR]
  LEFT JOIN DATA_IS 
  ON IDX_FS.CUSIP = DATA_IS.CUSIP AND DATA_BS.CUSIP = DATA_IS.CUSIP AND DATA_BS.[4DTYR] = DATA_IS.[4DTYR]
  LEFT JOIN DATA_SP 
  ON IDX_FS.CUSIP = DATA_SP.CUSIP AND DATA_BS.CUSIP = DATA_SP.CUSIP AND DATA_BS.[4DTYR] = DATA_SP.[4DTYR]
  -- LEFT JOIN DATA_Footnotes.[4DTYR] no need.

Hope this helps!!!

Collectives™ on Stack Overflow

SQL: JOIN syntax error

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related