CODEWITHSUNDEEP
sqlms-accessjoin
user2898577
user2898577

Reputation: 7

SQL: JOIN syntax error

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

Upvotes: 0

Views: 537

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

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:

  1. DO you want entire 'AND' condition(Line 15) to be added as a filter condition in ON clause when left joining with DATA_SP?
  2. 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!!!

Upvotes: 0

user806549
user806549

Reputation:

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?

Upvotes: 1

Related Questions