CODEWITHSUNDEEP
sqlms-accessjoin
John
John

Reputation: 11

JOIN Issues With SQL

I am getting the error: "Syntax Error In From Clause"

How do I fix this? Here is my SQL:

SELECT INVOICE.INV_NUMBER,
       PRODUCT_revised.P_DESCRIPT, 
       CUSTOMER.CUS_CODE, 
       CUSTOMER.CUS_LNAME, 
       CUSTOMER.CUS_FNAME, 
       LINE.LINE_UNITS AS [Purchase_Quantity], 
       LINE.LINE_PRICE AS [Unit_Price], 
       (Purchase_Quantity * Unit_Price) AS [Line Total]
FROM INVOICE, PRODUCT_revised, CUSTOMER, LINE
FULL JOIN INVOICE
     ON INVOICE.INV_NUMBER = LINE.INV_NUMBER;

Upvotes: 0

Views: 63

Answers (1)

Larry Lustig
Larry Lustig

Reputation: 50970

Your FROM clause is not even approximately correct. You are attempting to mix "old" and "new" JOINs. Choose one format, and stick with it. Probably best to use the newer format that specifically requires INNER JOIN for INNER JOINS. In my example below I'm assuming INNER JOIN is the type you want but depending on what you're really trying to extract it may not be the case.

In addition, you're missing JOIN conditions for most of the JOINs, so this query would spit out an enormous number of unwanted records.

In order to correctly write a JOIN that includes the same table twice (which is fine, and a common thing to do) you will need to alias those tables so they can be distinguished in the not-yet-supplied JOIN conditions.

Finally, FULL OUTER JOIN is rarely desired. Even if it is desired in this case MS Access doesn't support it.

So, you're eventual FROM clause will look something like this:

FROM ((((Invoice AS INV1 INNER JOIN Product_Revisied ON <JOIN Conditions Here>)
   INNER JOIN Customer ON <JOIN Conditions Here>)
   INNER JOIN Line ON <JOIN Conditions Here>)
   INNER-OR-OUTER JOIN Invoice AS INV2 ON <JOIN Conditions Here>)

Upvotes: 3

Related Questions